Concatenate row into column

Question

Hi 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

Sachin Singh · Answer

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( ' ' ))11 ' ' ) as Color from yourtable t1

Amit Mohanty · Answer

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 Ansari · Answer

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;

Gajendra Jangid · Answer

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