Solved

Alternative function for CONC in Sql server

Posted on 2013-11-17
4
634 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 250 total points
Comment Utility
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
Comment Utility
SELECT (ColumnA +','+ ColumnB) AS ColumnZ
FROM Table;
0
 
LVL 84
Comment Utility
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
Comment Utility
--
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now