SQL - Pivot Column To Rows with Group By and Concat

Question

I need to traspose a column into a row ( by concatenating it) with group by...

using : sql server 2019

Example

Car_ID	Car_Name	Owner
1	Ferrari	Marco
2	Jeep	Enrico
3	Mercedes	Matteo
1	Ferrari	Andrea
3	Mercedes	Giorgio
2	Jeep	Gianluca

How can i get this?

Car_ID	Car_Name	Owners
1	Ferrari	Marco,Andrea
2	Jeep	Enrico,Gianluca
3	Mercedes	Matteo,Giorgio

I tried something but i didn't get close enough to show something here.

Answer 1

Need to know your DB name to suggest appropriate answer. It's not pivot rather string aggregation. I will try to cover major RDBMs.

If you are using sql server or postgres you can use string_agg()

Query:

select Car_ID,Car_Name,string_agg(Owner,',') as Owners from Cars
group by Car_ID,Car_Name

Output:

Car_ID	Car_Name	Owners
1	Ferrari	Marco,Andrea
2	Jeep	Enrico,Gianluca
3	Mercedes	Matteo,Giorgio

fiddle

If you are using MySql then you can use group_concat():

Query:

select Car_ID,Car_Name,group_concat(Owner) as Owners from Cars
group by Car_ID,Car_Name

Output:

Car_ID	Car_Name	Owners
1	Ferrari	Marco,Andrea
2	Jeep	Enrico,Gianluca
3	Mercedes	Matteo,Giorgio

fiddle

If you are using Oracle then you can use listagg() for this purpose:

Query:

select Car_ID,Car_Name,listagg(Owner,',') as Owners from Cars
group by Car_ID,Car_Name

Output:

CAR_ID	CAR_NAME	OWNERS
3	Mercedes	Matteo,Giorgio
1	Ferrari	Marco,Andrea
2	Jeep	Enrico,Gianluca

fiddle