T-SQL: converting a variable number of rows to columns

Question

When I run an SQL query that returns a single column with a variable number of rows to return, I would like to convert each row to a VALUE column (I don't mind that the column header / header).

eg.

Column1 ------- a b c d e 

I want a script that converts the above to a table, for example:

 Col1 Col2 Col3 Col4 Col5 ------------------------ abcde 

(Note that I do not need column names).

I know that I cannot use PIVOT, because the line numbers are not fixed (they are based on the SQL query).

Any ideas?

Thanks!

Answer 1

You are trying to collapse your results and include a counter in your column name. Since I assume that you do not know the potential number of columns, you need to use Dynamic SQL for this.

This should be close using ROW_NUMBER to get a counter:

 declare @cols AS NVARCHAR(MAX), @colswithalias AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) set @colswithalias = STUFF((SELECT distinct ',' + QUOTENAME(col1) + ' AS Col' + CAST(ROW_NUMBER() OVER (ORDER BY col1) as varchar(10)) FROM yourtable FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @cols = STUFF((SELECT distinct ',' + QUOTENAME(col1) FROM yourtable FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT ' + @colswithalias + ' from ( select col1 rn, col1 from yourtable ) x pivot ( max(rn) for col1 in (' + @cols + ') ) p ' execute(@query) 

• SQL Fiddle Demo