Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Alternative function for CONC in Sql server

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

604 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