14

I have table TestTable

ID Name
-------
1  A
1  B
1  C 
2  D 
2  E
3  F

I want to write a query in SQL Server 2008 which will return 

ID Name
----------    
1   A,B,C
2   D,E
3   F

Please someone help me to write this query.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gulrej
  • 969
  • 4
  • 15
  • 25
  • have you tried with subqueries? – pinku Dec 28 '12 at 06:10
  • 1
    old question, but SQL Server 2017 introduced [string_agg](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) – Cleptus Mar 30 '22 at 12:31

3 Answers3

20

AFAIK, there is no native way to do so. However, you can use the FOR XML to do this like so:

SELECT 
  t1.Id,
  STUFF((
    SELECT ', ' + t2.name  
    FROM Table1 t2
    WHERE t2.ID = t1.ID
    FOR XML PATH (''))
  ,1,2,'') AS Names
FROM Table1 t1
GROUP BY t1.Id;

SQL Fiddle Demo

This will give you:

| ID |   NAMES |
----------------
|  1 | A, B, C |
|  2 |    D, E |
|  3 |       F |
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
3

try this ::

SELECT  a.ID, 
        SUBSTRING(d.Name,1, LEN(d.Name) - 1) Name
FROM
        (
            SELECT DISTINCT ID
            FROM testTable
        ) a
        CROSS APPLY
        (
            SELECT [Name] + ', ' 
            FROM testTable AS B 
            WHERE A.ID = B.ID 
            FOR XML PATH('')
        ) D (Name)
Ajith Sasidharan
  • 1,155
  • 7
  • 7
0

SQL server 2008 supports this by using Pivot or Unpivot @ http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Satinder singh
  • 10,100
  • 16
  • 60
  • 102
Melvin
  • 377
  • 2
  • 7
  • 19
  • 1
    It would be better if you show the OP how is the group concatenation can be done using the `PIVOT` table operator. – Mahmoud Gamal Dec 28 '12 at 06:25
  • 1
    Pivot will only create more columns, not put the pivoted data into ONE column, do not go down this path if you want one column. – ttomsen Jun 04 '14 at 14:29