SQL SERVER – Grouping by Multiple Columns to Single Column as A String

One of the most common questions I receive in email is how to group multiple columns 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.

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)

SQL SERVER - Grouping by Multiple Columns to Single Column as A String commatocol

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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Core Concepts – Elasticity, Scalability and ACID Properties – Exploring NuoDB an Elastically Scalable Database System
Next Post
SQL SERVER – Download Microsoft SQL Server Compact 4.0 SP1

Related Posts

54 Comments. Leave new

  • Mohammad Faisol Aminuddin
    August 20, 2013 7:41 am

    i have :

    tabel A : id || name ==> A1, A2, A3 || AZ, AX, XX
    tabel B : idd || nameB ==> B1, B2, B3, B4 || BQ, BV, BC, BB
    tabel C : A_ID || B_ID ( foreign key from tabel A and B ) ==> A1, A1, A2, A2, A3 || B2, B1, B1, B3, B4

    I want the result like this :

    name || nameB
    AZ || BV, BQ
    AX || BQ, BC
    XX || BB

    This is my query, but name column won’t come out :

    SELECT A.NAMA, STUFF (
    (select ‘ , ‘ + B.NIT_ID
    FROM JTAB B
    WHERE J.NIK_ID = B.NIK_ID
    FOR XML PATH(”)),1,2,”) AS RES
    FROM Ahli A, TRAINING T, JTAB J
    WHERE J.NIK_ID = A.NIK AND J.NIT_ID = T.NIT
    GROUP BY J.NIK_ID, A.NAMA

    help me please ….

    Reply
  • How to use more than one AND operator on same field?

    Reply
  • in mysql is having simple function for this like Group_concat() in sql server its very big syntax to write..

    Reply
  • Nilesh Amruskar
    August 15, 2014 12:17 pm

    Thanx for the above query!! :)

    Reply
  • i want output like this

    ID CSV
    1 A
    B
    C
    2 A
    B
    C
    D
    E

    CSV is Multiline Column.
    Please reply…
    THanx in Advance.

    Reply
  • It would be nice if MS SQL supports group_concat:
    SELECT t.id, GROUP_CONCAT(t.col)
    FROM TestTable t
    GROUP BY t.id

    Reply
  • is it possible with charindex() function

    Reply
  • My question is
    this my string –> SQL Server
    and i want output like-> SQL
    Server
    using charindex()

    Reply
  • thank you sir

    Reply
  • a function that will accept a string as input parameter and will encrypt the data as explained below –

    1. replace each alphabet within this string with it’s corresponding reverse alphabet

    Reply
  • • A with Z, B with Y, C with X …so on
    2) Similarly replace numbers – 0 with 9, 1 with 8, 2 with 7, 3 with 6, 4 with 5, 5 with 4, 6 with 3, 7 with 2, 8 with 1 & 9 with 0
    3). All characters other than alphabets & numbers and having ASCII code less than 128 should be replaced by corresponding Extended ASCII characters. (Extended code = ASCII code of current character + 128)

    Reply
  • sir will you explain brifely please

    Reply
  • Q)a stored procedure that will accept two input string parameters and will encrypt the data as explained below –

    First parameter should be the message to encrypt and second parameter should be the key for encryption. The encryption key must be min. 6 to 8 characters mix of alphabets & numbers. Procedure should perform encryption by adding the key to message. In addition to this, the procedure should also decrypt back the message using same key.

    Reply
  • I’ve used this methodology before… very nice! Quick question – how would I add in the ID field to the CSV output field.
    Ex: CSV column output = Row 1: 1,A,B,C…. Row 2: 2,A,B,C,D,E…. etc.
    I can get it in my CSV output, but it repeats like so: 1,A,1,B,1,C…

    Any help is greatly appreciated!

    Reply
  • sachin darakhe
    August 3, 2015 2:54 pm

    I having scenario as follows

    Col1 Col2 Col3
    A 1 2
    A 3 4
    B 5 6

    solution as follows which should have better performance

    Col1 Col2 Col3
    A 1,3 2,4
    B 5 6

    Reply
  • Denomination

    0.01,0.02,0.05,0.10,0.20,0.50,1,2,5,10,20,50
    1,2,5,10,20,50,100
    0.5,1,2,5,10,20,50,100
    0.5,1,2,5,10,20,50,100
    50,100,200,500,1000
    50,100,200,500,1000
    1,5,10,20,50,100

    In the above Denomination column i want to get the smallest of each row. Please can any help on this.
    For example 1st row should return 0.01, 2nd row should return 1 ………

    Reply
  • Its very difficult to find an article u r searching for in this blog. I searched Group By , it gave me 81 pages of results. 2 years back this was my favorite SQL site, I used to suggest to my friends about this site,I searched for 30 min in 81 pages then I had no patience I moved on to another SQL site.
    This is really unexpected from SQL authority site.

    Reply
  • Sometimes my returns have multiple concatenated numbers but some of the numbers are the same. How is that happening?

    Reply
  • Coskun Olcucu
    May 30, 2017 1:54 am

    Hi I have to do the same thing in SQL.
    I have question regarding your code:
    what is t.id, s.id and s.col?

    Reply
  • Thank you Pinal. It helps!!

    Reply

Leave a Reply

Menu