Tsql all ids and values in one row

Question

How can I select all ids and values in one row, group by condition?

For example:

| id | value | condition |
--------------------------
| 1 | value1 |     0     |
| 2 | value2 |     0     |
| 3 | value3 |     1     |
| 4 | value4 |     1     |

Result:

| ids  |     values     | condition |
-------------------------
| 1, 2 | value1, value2 |     0     |
| 3, 4 | value3, value4 |     1     |

Google: "SQL Server aggregate string concatenation" or "SQL Server group_concat". — Gordon Linoff, Apr 04 '16 at 12:00

score 0 · Accepted Answer · answered Apr 04 '16 at 12:12

Try it like this:

CREATE TABLE #test(id INT,value VARCHAR(100),condition INT);
INSERT INTO #test VALUES
 (1,'value1',0)
,(2,'value2',0)
,(3,'value3',1)
,(4,'value4',1);

WITH MyDistinctConditions AS
(
    SELECT DISTINCT condition
    FROM #test 
)
SELECT c.condition
      ,(SELECT STUFF(
                      (
                      SELECT ', ' + CAST(t.id AS VARCHAR(10)) 
                      FROM #test AS t 
                      WHERE t.condition=c.condition
                      FOR XML PATH('')),1,2,'')) AS ids
      ,(SELECT STUFF(
                      (
                      SELECT ', ' + t.value 
                      FROM #test AS t 
                      WHERE t.condition=c.condition
                      FOR XML PATH('')),1,2,'')) AS [values]
FROM MyDistinctConditions AS c;

DROP TABLE #test;

The result

condition   ids     values
0           1, 2    value1, value2
1           3, 4    value3, value4

Tsql all ids and values in one row

1 Answers1