Convert rows into comma separated column using single query with XML

Convert rows into comma separated column using single query with XML

Have you ever try to covert list of rows into an comma separated column? There are many solution available based on the coalesce but I found an interesting fact about the XML Path by which we  can easily achieve the same functionality without much of hassle.

In the below example I have created a query to extract table and column names from the information_schema  for two test tables

SELECT table_name, 
       column_name 
FROM   information_schema.columns 
WHERE  table_name IN ( 'DimProductCategory', 'DimCurrency' ) 

 

output

Now if we just want to display two rows for each table and concatenate the columns into single value with a delimiter then we can simply achieve this by below query.

SELECT Distinct col2.table_name, 
       Stuff((SELECT ',' + column_name
              -- Stuff used here only to strip the first character which is comma (,). 
              FROM   information_schema.columns col1 
              WHERE  col1.table_name = col2.table_name 
              FOR xml path ('')), 1, 1, '') 
FROM   information_schema.columns col2 
WHERE  table_name IN ( 'DimProductCategory', 'DimCurrency' ) 

Further Learning

Stuff : http://msdn.microsoft.com/en-us/library/ms188043.aspx 

FOR XML PATH : http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/