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
Solved

Alternative function for CONC in Sql server

Posted on 2013-11-17
4
656 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
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 84
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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 the fundamental information of how to create a table.

839 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