2013-05-19

Multiple rows to single column

Last year I had a challenge to find a solution for transform rows in a single column using only one query.
The initial query was this one:

SELECT object_name(o.object_idc.name
FROM sys.objects o
INNER JOIN sys.columns on (c.object_id o.object_id)
WHERE o.type 'U'


My solution was the following script that returns all columns names from all user tables in the database context. It returns one row by user table.

SELECT object_name(o.object_id),
      STUFF(
            (SELECT ','+ c.name
            FROM sys.columns c
            WHERE c.object_id = o.object_id 
                 for XML path(''),type).value('.','NVARCHAR(MAX)')
            ,1,1,'')
FROM sys.objects o

WHERE o.type = 'U'


FOR XML PATH('') instruction do the trick for returning all columns names into a single column (XML string). The parameter ('') is for eliminate the "<row>" and "</row"> tag.
*The TYPE parameter it is for handling eventual special characters as '<', '>', '&', '/', etc.

STUFF function, deletes a specified length of characters and inserts another set of characters at a specified starting point. In this case only deletes the comma in the first position.

edit*: I want to thanks Jeff Moden for his input about this article. He made some pertinent comments about the TYPE parameter for XML PATH that made me add it and called me attention for improve the solution with only one sub-select, what I also did.