eZeeNow.com

it's your's

Concatenate/Convert Rows In SQL using FOR XML PATH()

How to concatenate/convert table rows to columns ?

We can convert rows to columns this can be done by using FOR XML PATH() and STUFF function.

USE MSDB;
select * from INFORMATION_SCHEMA.COLUMNS


Following query will convert rows to columns. It will concatenate the table columns to a comma separated list with the table name.

SELECT    DISTINCT  TABLE_NAME  ,
STUFF(( SELECT ',' + COLUMN_NAME
-- Add a comma (,) before each value
FROM INFORMATION_SCHEMA.COLUMNS as ISC
WHERE
ISC.TABLE_NAME = ISC2.TABLE_NAME
FOR XML PATH('') --Select it as XML
), 1, 1, '' )
-- This is done to remove the first character (,)
-- from the result
AS COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS ISC2
OUTPUT.



Loading