Select date values to column names

Question

I have a table like this

item  value   date
-----------------------
a      8.9    20170101
b     10.3    20170114
a      3.4    20170111
c     13.4    20170121
b      8.3    20170111
b      1.0    20170312

I want to run some select to get it to show up like this

    20170101      20170111    20170114   20170121  20170312
a     8.9            3.4
b                    8.3         10.3                  1.0
c                                            13.4

Is this possible in T-SQL?

Dean-O

Sounds like a job for PIVOT - unfortunately, given the probable variations of values in `date` you will have to go with the myriad of dynamic PIVOT solutions scattered across this site and others. — Forty3, Feb 05 '18 at 21:01
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Tab Alleman, Feb 05 '18 at 21:30

score 2 · Accepted Answer · answered Feb 05 '18 at 21:35

This can be accomplished using a pivot table.

If you know all the dates you could use:

select * from(select item,value,date from pivotExample) ot
pivot(sum(value) for date in ([20170101],[20170111],[20170114],[20170121],[20170312])) pt

If you don't know the dates, you can dynamically generate using:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(date) 
                from pivotExample
                group by date
                order by date
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT item,' + @cols + ' from 
         (
            select item,value,date
            from pivotExample
        ) x
        pivot 
        (
            sum(value)
            for date in (' + @cols + ')
        ) p '

execute(@query);

I don't know all the dates. They are random dates for each value — Dean-O, Feb 06 '18 at 12:58

Select date values to column names

1 Answers1