CODEWITHSUNDEEP
sql-servert-sqlrows
Dean-O
Dean-O

Reputation: 1153

Select date values to column names

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

Upvotes: 1

Views: 353

Answers (1)

Darthchai
Darthchai

Reputation: 755

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);

Upvotes: 2

Related Questions