SQL select multiple rows in one column

sql, sql-server, sql-server-2008, t-sql

Solution

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 |

Problem

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.

Original source