SQL Server Pivot with Dynamic Fields

Question

Trying to figure out the best way to pivot a table dynamically on the date field in the following table. the issue is these dates change so I cant hard code them in the pivot statement.

id value date
1  55    2012-12-29 00:00:00:00
1  54    2012-10-29 00:00:00:00
1  89    2013-02-02 00:00:00:00
2  45    2012-12-29 00:00:00:00
2  54    2012-10-29 00:00:00:00
4  78    2012-12-29 00:00:00:00
4  90    2012-10-29 00:00:00:00
4  23    2012-10-29 00:00:00:00

I want the output to look like this

id 2012-12-29 00:00:00:00 2012-10-29 00:00:00:00 2013-02-02 00:00:00:00
1   55                    54                     89
2   45                    54                     null
4   78                    90                     23

possible duplicate of SQL Server dynamic PIVOT query?

RichardTheKiwi
– RichardTheKiwi

2013-05-03 09:25:00 +00:00
Commented May 3, 2013 at 9:25 — RichardTheKiwi
– RichardTheKiwi, Commented May 3, 2013 at 9:25

Taryn · Accepted Answer · 2013-02-15 20:20:53Z

Since you are using SQL Server, then you can use the PIVOT function.

If your values are known, then you can hard-code the values:

select *
from
(
  select id, value, date
  from yourtable
) src
pivot
(
  max(value)
  for date in ([2012-12-29], [2012-10-29], [2013-02-02])
) piv

See SQL Fiddle with Demo.

But if they are unknown, then you will need to use dynamic sql:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(50), date, 120)) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + ' from 
             (
                select id, value, convert(varchar(50), date, 120) date
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for date in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo.

The result of both queries is:

| ID | 2012-10-29 00:00:00 | 2012-12-29 00:00:00 | 2013-02-02 00:00:00 |
------------------------------------------------------------------------
|  1 |                  54 |                  55 |                  89 |
|  2 |                  54 |                  45 |              (null) |
|  4 |                  90 |                  78 |              (null) |

rs. · Accepted Answer · 2013-02-15 20:21:57Z

0

try this

DECLARE @columns VARCHAR(8000)

-- set columns for pivot
SELECT @columns = COALESCE(@columns + ',[' + cast([date] as varchar(50)) + ']', '[' + cast([date] as varchar(50))+ ']')         
    FROM  TableName

EXEC(' SELECT *, ' +  @columns + ' FROM 
       (
           SELECT * FROM TableName
       ) As a
       PIVOT (MAX(value) FOR DATE IN (' + @columns + ') ) As P')

answered Feb 15, 2013 at 20:21

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Collectives™ on Stack Overflow

SQL Server Pivot with Dynamic Fields

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related