?
Solved

Alternative function for CONC in Sql server

Posted on 2013-11-17
4
Medium Priority
?
672 Views
Last Modified: 2013-11-24
We are using the MS access CONC function in order to merge rows for services quotes, so that if multiple values are present between rows for a single quote we merge them and separate the values by commas.

We need to run below query on Sql server database but the CONC built-in function is not available in SQL server.  Please suggest which Sql function we can use in place of CONC in Sql server.

Query:
SELECT [SVC].[ID1],
CONC("Market","ID1",[ID1],"SVC") AS Market,
CONC("Location","ID1",[ID1],"SVC") AS Location,
CONC("Requestor","ID1",[ID1],"SVC") AS Requestor,
CONC("HW Quote Generated By","ID1",[ID1],"SVC") AS [HW Quote Generated By],
CONC("Cabinet","ID1",[ID1],"SVC") AS Cabinet
From Temp_Name
0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 12

Accepted Solution

by:
Tony303 earned 1000 total points
ID: 39655661
SELECT SVC.ID1,
'Market,' + 'ID1,' + SVC.ID1 + ',SVC' AS Market,
'Location,' + 'ID1,' + SVC.ID1 + ',SVC' AS Location,
'Requestor,' + 'ID1,' + SVC.ID1 + ',SVC' AS Requestor,
'HW Quote Generated By,' + 'ID1,' + SVC.ID1 + ',SVC' AS [HW Quote Generated By],
'Cabinet,' + 'ID1,' + SVC.ID1 + ',SVC' AS  Cabinet
From Temp_Name 

Open in new window


I am wondering why the [ID1] field you are using is being used to define the market, location, requestor, HW Quote Generated By and Cabinet.
Surely you would want various fields.

anyhow, the above is how to do the same in SQL.
You may have to convert the SVC.ID1 field to text however if it is an integer say.

You could do this as the convert....CONVERT(VARCHAR(10), SVC.ID1)

I hope this helps.
T
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39655681
SELECT (ColumnA +','+ ColumnB) AS ColumnZ
FROM Table;
0
 
LVL 85
ID: 39656081
To be clear: There is no CONC function in Access. This is something you've picked up from somewhere else.
0
 

Author Closing Comment

by:sqldba2013
ID: 39672606
--
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question