asked
how to concatenate row value in a column
NO NAME COMPANY SCHEME 1 JOHN TATA TATA1 2 JOHN TATA TATA2 3 JOHN BIRLA BIRLA1 4 JOHN BIRLA BIRLA2 i am getting an output of the query like above, but i need to get .. as customer name is same and company is same but only scheme is differ, instead of getting two rows i need to fetch only one row..in that scheme as to come by , separated ... like NO NAME COMPANY SCHEME 1 JOHN TATA TATA1,TATA2
This Article is pretty concise [
http://www.projectdmx.com/tsql/rowconcatenate.aspx][1] [1]:
http://www.projectdmx.com/tsql/rowconcatenate.aspx
Murali, you didn't mention the version of SQL Server, but this will work for 2005 and newer: WITH TestData([NO], NAME, COMPANY) AS (SELECT 1, 'JOHN TATA', 'TATA1' UNION SELECT 2, 'JOHN TATA', 'TATA2' UNION SELECT 3, 'JOHN BIRLA', 'BIRLA1' UNION SELECT 4, 'JOHN BIRLA', 'BIRLA2') SELECT Name,Companies = STUFF ((SELECT ',' + Company FROM TestData c WHERE c.NAME = t.NAME FOR XML PATH ('')),1,1,'') FROM TestData t GROUP BY NAME
