Concatenate row into column

Ramco Ramco

4y904SQL Server

I have below data

Car Color

Ford Blue

Ford Black

Honda Blue

I want to display like this. Can it be done using Concat or Coalesce

Ford Blue , Black

Honda Blue

Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Just remember one thing, whenever you have to convert rows into column as a comma separated value, the easiest way is to use for xml path with a correlated subquery and lastly do the stuff to cut the first comma.
select Car,
stuff((select  ","+color from yourtable t2
where t2.Car=t1.Car for xml path(' ')),1,1,' ') as Color
from yourtable t1
Gajendra Jangid4y ago
Try This link
https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/

or u can use like this
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp(Name nvarchar(50), Colour nvarchar(50))
INSERT INTO #temp VALUES('Ford', 'Blue')
INSERT INTO #temp VALUES('Ford', 'Black')
INSERT INTO #temp VALUES('Honda', 'Blue')


SELECT Name, Colour = STUFF((SELECT ', ' + Colour FROM #temp t2 WHERE t2.Name = t1.Name FOR XML PATH('')), 1, 1, '')
FROM #temp t1 GROUP BY Name
Amit Mohanty4y ago
Hey, if you are using SQL Server 2017 or more then you can use STRING_AGG() function as per Muhammad Imran Ansari.
But if you use lower version of SQL Server then you can try with below query.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp(Name nvarchar(50), Colour nvarchar(50))
INSERT INTO #temp VALUES('Ford', 'Blue')
INSERT INTO #temp VALUES('Ford', 'Black')
INSERT INTO #temp VALUES('Honda', 'Blue')


SELECT Name, Colour = STUFF((SELECT ', ' + Colour FROM #temp t2 WHERE t2.Name = t1.Name FOR XML PATH('')), 1, 1, '')
FROM #temp t1 GROUP BY Name
Muhammad Imran Ansari4y ago
You can use STRING_AGG() built-in function which works in SQL Server 2017 ownward and Azure SQL Database. Query is like:
SELECT Car, STRING_AGG(Color, ', ') AS Colors
FROM tblCarColor
GROUP BY Car;