How to concatenate multiple rows?

Question

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

possible duplicate of How do I Create a Comma-Separated List using a SQL Query? and of stackoverflow.com/questions/3087684/… @Martin this last one has a better example of using it in a 'GROUP BY' There is also an option of using a custom CLR aggregate. — Martin Smith
– Martin Smith, Commented Jun 25, 2010 at 20:05
Maybe I missed something here, but where are you getting the extra data from? — VoodooChild
– VoodooChild, Commented Jun 25, 2010 at 20:06
SqlServer 2017 now has STRING_AGG that aggregates multiple strings into one using a given separator. (Not allowed to post a duplicate answer to a duplicate question.) — John
– John, Commented Aug 31, 2017 at 11:19

OMG Ponies · Accepted Answer · 2011-02-17 16:26:07Z

3

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...

edited Feb 17, 2011 at 16:26

answered Jun 25, 2010 at 20:18

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KM. Over a year ago

this does not properly handle XML special characters like > and &. So if your_table.data contained one & two you'd get one & two in the result set. There is a nifty way to handle this, see: stackoverflow.com/questions/5031204/…

Gabriel McAdams · Accepted Answer · 2010-06-25 20:08:18Z

0

I don't have my code in front of me, or I would show you a quick example, but I would look into writing a CLR aggregate for this. Its very simple. There are some automatically created method to use, and they're just for collection (add to a List<> object or something), Merge (merging multiple lists created in multiple threads), and an output (take the list and turn it into a string - String.Join(",", list.ToArray())). Only thing to know is that there is a length limit of 8000 characters.

answered Jun 25, 2010 at 20:08

Gabriel McAdams

58.5k12 gold badges67 silver badges78 bronze badges

Collectives™ on Stack Overflow

How to concatenate multiple rows?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related