One of the most common questions I receive in email is how to group multiple column data in comma separate values in a single row grouping by another column.
I have previously blogged about it in following two blog posts. However, both aren’t addressing the following exact problem.
Comma Separated Values (CSV) from Table Column
Comma Separated Values (CSV) from Table Column – Part 2
The question comes in many different formats but in following image I am demonstrating the same question in simple words. This is the most popular question on my Facebook page as well. (Example)
Here is the sample script to build the sample dataset.
CREATE TABLE TestTable (ID INT, Col VARCHAR(4))
GO
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E'
GO
SELECT *
FROM TestTable
GO
Here is the solution which will build an answer to the above question.
-- Get CSV values
SELECT t.ID, STUFF(
(SELECT ',' + s.Col
FROM TestTable s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO
I hope this is an easy solution. I am going to point to this blog post in the future for all the similar questions.
Final Clean Up Act
-- Clean up
DROP TABLE TestTable
GO
Here is the question back to you -
Is there any better way to write above script? Please leave a comment and I will write a separate blog post with due credit.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: SQL XML
