Pivot dynamic column

Question

I have a table with the following values.

Date                     TCOUNT      COUNT
02/06/2013 00:00        3500        35
02/12/2013 00:00        4000        23
02/21/2013 00:00        1000        54
02/27/2013 00:00        5000        12

where Date is dynamic col need to be pivoted.

I need to pivote the above table to get the below result

02/06/2013 00:00    02/12/2013 00:00    02/21/2013 00:00    02/27/2013 00:00
35                  23                  54                  12
3500                4000                1000                5000

please help.

Thanks.

possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Kyle Hale, Apr 24 '14 at 18:03

score 2 · Accepted Answer · edited May 23 '17 at 12:12

Based on the fact that your previous question was tagged with sql server, I am guessing that you need sql server syntax.

In order to get the result, you will need to use both the UNPIVOT and the PIVOT functions. The unpivot will take the TCount and Count columns and convert them to rows and then the PIVOT will take the dates and convert them to columns.

If you know the values ahead of time then you can hard-code the query:

select *
from
(
  select date, value, col
  from yourtable
  unpivot
  (
    value
    for col in (tcount, count)
  ) unpiv
) src
pivot
(
  max(value)
  for date in ([2013-02-06], [2013-02-12], 
               [2013-02-21], [2013-02-27])
) piv;

See SQL Fiddle with Demo

However, if you have an unknown number of dates, then you will need dynamic SQL:

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

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

set @query = 'SELECT col, ' + @cols + ' from 
             (
                select convert(varchar(10), Date, 120) date, 
                  value, col
                from yourtable
                unpivot
                (
                  value
                  for col in (tcount, count)
                ) unpiv
            ) src
            pivot 
            (
                max(value)
                for date in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

The result of both is:

|    COL | 2013-02-06 | 2013-02-12 | 2013-02-21 | 2013-02-27 |
--------------------------------------------------------------
|  COUNT |         35 |         23 |         54 |         12 |
| TCOUNT |       3500 |       4000 |       1000 |       5000 |

Why, oh why, can't Microsoft give us true dynamic pivoting? Blearg. — ErikE, Feb 12 '13 at 21:56
It's probably due to complex engine internals that mere mortals cannot comprehend without an endowment of wit straight from the gods. — ErikE, Feb 12 '13 at 21:59
No, it's due to relational theory, in which the columns in a given query's result set are fixed. The values found in matching rows cannot magically add columns. Relational theory is widely understood, without divine intervention. :-) — Bill Karwin, Feb 12 '13 at 22:55

Pivot dynamic column

1 Answers1