Dynamic PIVOT and cross-tab query (SQL Server)

SQL Server > Operators > PIVOT > Dynamic PIVOT and cross-tab query




Sometimes we don't know values in FOR clause for pivot.
In this example we will build dynamic columns with values and use dynamic query to build pivot and cross-tab query.

Example:

create table pivot_data

(
       id           int,
       Date   date,
       amount int
)
set nocount on
go

insert into
       pivot_data (id, Date, amount)
values
       (1,GETDATE(),100)
insert into
       pivot_data (id, Date, amount)
values
       (1,dateadd(day,1, GETDATE()),200)
insert into
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,1, GETDATE()),300)
insert into
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,2, GETDATE()),400)    

select * from pivot_data

Result:

id            Date                    amount

1             2013-03-04         100
1             2013-03-05         200
2             2013-04-04         300
2             2013-05-04         400

DECLARE

    @cols AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT  ',' + QUOTENAME(c.Date )
            FROM pivot_data c
            order by Date
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
print @cols

set @query = 'SELECT id, ' + @cols + ' from

            (
                select
                    id
                    , date
                    , amount
                from
                    pivot_data
           ) x
            pivot
            (
                sum(amount)
                for  date in (' + @cols + ')
            ) p '

execute(@query)

Result:

id 2013-03-04    2013-03-05        2013-04-04        2013-05-04

1 100                 200                     NULL                 NULL
2 NULL              NULL                 300                     400

drop table pivot_data