Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Alternative function for CONC in Sql server

Posted on 2013-11-17
4
Medium Priority
?
687 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
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

564 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