vineetbhargav (11/30/2015)
Create table #tab(ID int , COLA char(5), COLB char(5) ,COLC char(5))INSERT INTO #tab(ID ,COLA, COLB, COLC)
VALUES(1001,'1AAA' ,null, null), (1001 ,null,'2BBB', null),(1001,null,null, '3CCC')
SELECT
ID,
STUFF((SELECT ';' + coalesce(COLA, COLB, COLC)
FROM #tab tab1
WHERE tab1.ID = tab2.ID
FOR XML PATH('')), 1, 1, '') ALLCOL
FROM #tab tab2
GROUP BY tab2.ID
ORDER BY 1
This produces the same output as the code I posted. While this does demonstrate a great technique for creating delimited lists it is a bit overkill here. Also you should break the habit of ordering by ordinal position. You should use the column name instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/