TSQL Select One To Many. Concatenate Many to one column / row in Select statement

Question

I have a select statement with a one to many.

I would like the results from a column from the Many to show up as a Column in one row. What is the easiest way.

Table 1

1   Joe   
2   Jill
3   Bill
4   Jennifer

Table 2

1    Red
1    Yellow
1    Blue
2    Green
2    Black
4    Purple

Results

1   Joe      Red, Yellow, Blue
2   Jill     Green, Black
3   Bill
4   Jennifer Purple

Look for TSQL equivalent for group_Concat() or List_Agg() and you'll find what you need using stuff and for xml path https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f09d4166-2030-41fe-b86e-392fbc94db53/tsql-equivalent-for-groupconcat-function?forum=transactsql or possibly `string_Agg()` in MSFT depending on version of db. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql So... what version of tSQL (assuming MSFT and not Sybase which is also tSQL)? — xQbert, Aug 09 '17 at 13:44
I suppose you could use a recursive CTE https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings shows a couple examples the for xml path seems to be the most efficient though it wasn't the accepted answer. If string_Agg() is available https://sqlperformance.com/2016/12/sql-performance/sql-server-v-next-string_agg-performance it seems to be the top dog for performance. — xQbert, Aug 09 '17 at 13:58
Possible duplicate of [Optimal way to concatenate/aggregate strings](https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) — xQbert, Aug 09 '17 at 14:07

score 1 · Answer 1 · answered Aug 09 '17 at 14:14

If you are using Sql server 2017 or SQL Azure then you can use String_Agg as below:

Select t1.Id, t1.Name, String_agg(color,',') from #table1 t1
left join #table2 t2 on t1.Id = t2.Id
Group by t1.Id, t1.Name

If you are using SQL Server <= 2016 then you can use query as below:

Select t1.Id, t1.Name, Colors = stuff((select ','+color from #table2 tt2 where tt2.id = t1.id for xml path('')),1,1,'')
    From #table1 t1
Group by t1.Id, t1.Name

Output as below:

+----+----------+-----------------+
| Id |   Name   |     Colors      |
+----+----------+-----------------+
|  1 | Joe      | Red,Yellow,Blue |
|  2 | Jill     | Green,Black     |
|  3 | Bill     | NULL            |
|  4 | Jennifer | Purple          |
+----+----------+-----------------+

score 0 · Answer 2 · answered Aug 09 '17 at 14:06

If you have 2017 or Azure then string_agg() is available and use it. If not then the for xml path approach shown:

are likely your best bet.

But as you can see in Aaron's blog: https://sqlperformance.com/2016/12/sql-performance/sql-server-v-next-string_agg-performance string_Agg() is the way to go if you can.

TSQL Select One To Many. Concatenate Many to one column / row in Select statement

2 Answers2