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' )
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' )
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
