T-SQL:Transform variable number of rows into columns

Question

When I run a SQL query which returns one column with variable number of rows returned, I'd like to transform each of the row into column VALUE (I don't mind what the column header/titles are).

E.g.

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

I want a script which will transform the above into a table like:

Col1 Col2 Col3 Col4 Col5
------------------------
a    b    c    d    e

(Note that I do not care for the column names).

I know I cannot user PIVOT as row numbers are not fixed (they are based on a SQL query).

Any ideas?

Thanks!

possible duplicate of : stackoverflow.com/questions/13103114/… — JanR
– JanR, Commented Jun 25, 2013 at 2:29

sgeddes · Accepted Answer · 2013-06-25 02:53:56Z

3

You're trying to pivot your results and include a counter in your column name. Since I'm presuming you don't know the potential number of columns, you'll need to use Dynamic SQL to accomplish this.

This should be close using ROW_NUMBER to get the 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

answered Jun 25, 2013 at 2:53

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

viv_acious Over a year ago

a quick question: do you know how I can store the output of that execute(@query) into a temp table? Tried several ways but did not work!

viv_acious Over a year ago

Thanks so much for your super quick response! I tried that as well. My execute(@query) returns around 600 columns. So when I execute the temp table, I get the error msg: Cannot create a row of size 14397 which is greater than the allowable maximum row size of 8060.

Collectives™ on Stack Overflow

T-SQL:Transform variable number of rows into columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related