SQL complex dynamic Pivoting 2

Question

Hi I am trying in SQL Server the pivoting for the following table

REFID | COL1 | COL2 | Sequence
1       abc    cde     1
1       lmn    rst     2
1       kna    asg     3
2       als    zkd     2
2       zpk    lad     1

I want the output as

        REFID | 1COL1 | 2COL1 | 3COL1 |1COL2|2COL2|3COL2
           1    abc     lmn      kna    cde   rst   asg
           2    zpk     als      null   lad   zkd   null

The number of columns in the original table are known but the number of rows are not known. Can any one help

Answer 1

If you want to include the sequence number as part of your column names, then you will still need to unpivot your col1 and col2 columns first, then apply the pivot. The difference is that you will concatenate the sequence number to your column names created during the unpivot process.

For a known number of values the query would be:

select REFID, 
    [1col1], [2col1], [3col1],
    [1col2], [2col2], [3col2]
from 
(
    select REFID, 
        col = cast(Sequence as varchar(10))+ col, value
    from yourtable
    cross apply
    (
        select 'COL1', col1 union all
        select 'COL2', col2
    ) c (col, value)
) d
pivot
(
    max(value)
    for col in ([1col1], [2col1], [3col1],
                [1col2], [2col2], [3col2])
) piv
order by refid;

Then if you have an unknown number the dynamic SQL version will be:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(Sequence as varchar(10))+ col) 
                    from yourtable
                    cross apply
                    (
                        select 'Col1', 1 union all
                        select 'Col2', 2
                    ) c(col, so)
                    group by Sequence, col, so
                    order by  so, sequence
                    FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT refid, ' + @cols + ' 
            from 
            (
               select REFID, 
                    col = cast(Sequence as varchar(10))+ col, value
                from yourtable
                cross apply
                (
                    select ''COL1'', col1 union all
                    select ''COL2'', col2
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p 
            order by refid'

execute sp_executesql @query;