SQL Server – How to Concatenate Multiple Rows

sqlsql-serversql-server-group-concatstringt-sql

I have the following query which returns the salary of all employees. This work perfectly but I need to collect extra data that I will aggregate into one cell (see Result Set 2).

How can I aggregate data into a comma separated list? A little bit like what Sum does, but I need a string in return.

SELECT Employee.Id, SUM(Pay) as Salary
FROM Employee
INNER JOIN PayCheck ON PayCheck.EmployeeId = Employee.Id
GROUP BY Employee.Id

Result Set 1

Employee.Id              Salary
-----------------------------------
          1                 150
          2                 250
          3                 350

I need:

Result Set 2

Employee.Id              Salary                 Data
----------------------------------------------------
          1                 150      One, Two, Three
          2                 250      Four, Five, Six
          3                 350      Seven

Best Answer

For SQL Server 2005+, use the STUFF function and FOR XML PATH:

WITH summary_cte AS (
   SELECT Employee.Id, SUM(Pay) as Salary
     FROM Employee
     JOIN PayCheck ON PayCheck.EmployeeId = Employee.Id
 GROUP BY Employee.Id)
SELECT sc.id, 
       sc.salary,
       STUFF((SELECT ','+ yt.data
                FROM your_table yt
               WHERE yt.id = sc.id
            GROUP BY yt.data
             FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
  FROM summary_cte sc

But you're missing details about where the data you want to turn into a comma delimited string is, and how it relates to an employee record...

Related Solutions

SQL Server – How to Concatenate Multiple Rows into One Column

Ok, I feel like I need to jump in to comment about How do you concat multiple rows into one column in SQL Server? and provide a more preferred answer.

I'm really sorry, but using scalar-valued functions like this will kill performance. Just open SQL Profiler and have a look at what's going on when you use a scalar-function that calls a table.

Also, the "update a variable" technique for concatenation is not encouraged, as that functionality might not continue in future versions.

The preferred way of doing string concatenation to use FOR XML PATH instead.

select
 stuff((select ', ' + t.tag from tags t where t.photoid = p.photoid order by tag for xml path('')),1,2,'') as taglist
 ,*
from photos
order by photoid;

For examples of how FOR XML PATH works, consider the following, imagining that you have a table with two fields called 'id' and 'name'

SELECT id, name
FROM table
order by name
FOR XML PATH('item'),root('itemlist')
;

Gives:

<itemlist><item><id>2</id><name>Aardvark</a></item><item><id>1</id><name>Zebra</name></item></itemlist>

But if you leave out the ROOT, you get something slightly different:

SELECT id, name
FROM table
order by name
FOR XML PATH('item')
;

<item><id>2</id><name>Aardvark</a></item><item><id>1</id><name>Zebra</name></item>

And if you put an empty PATH string, you get even closer to ordinary string concatenation:

SELECT id, name
FROM table
order by name
FOR XML PATH('')
;

<id>2</id><name>Aardvark</a><id>1</id><name>Zebra</name>

Now comes the really tricky bit... If you name a column starting with an @ sign, it becomes an attribute, and if a column doesn't have a name (or you call it [*]), then it leaves out that tag too:

SELECT ',' + name
FROM table
order by name
FOR XML PATH('')
;

,Aardvark,Zebra

Now finally, to strip the leading comma, the STUFF command comes in. STUFF(s,x,n,s2) pulls out n characters of s, starting at position x. In their place, it puts s2. So:

SELECT STUFF('abcde',2,3,'123456');

gives:

a123456e

So now have a look at my query above for your taglist.

select
 stuff((select ', ' + t.tag from tags t where t.photoid = p.photoid order by tag for xml path('')),1,2,'') as taglist
 ,*
from photos
order by photoid;

For each photo, I have a subquery which grabs the tags and concatenates them (in order) with a commma and a space. Then I surround that subquery in a stuff command to strip the leading comma and space.

I apologise for any typos - I haven't actually created the tables on my own machine to test this.

Rob

Best Answer

Related Solutions

SQL Server – How to Concatenate Multiple Rows into One Column

Related Question