0

I just know a few things about SQL Server, but i want to transpose a table with a daily values of a couple of ID's.

I've searching for a query example without having results. I want an explanation for group by date sintaxys for my transposed table.

This is an example of my original table:

DATE        ID  VALUE
2010-04-05  a   8
2010-04-05  b   7
2010-04-05  c   6
2010-04-05  d   5
2010-04-06  a   9
2010-04-06  b   8
2010-04-06  c   7
2010-04-06  d   6
2010-04-07  a   10
2010-04-07  b   9
2010-04-07  c   8
2010-04-07  d   7

And i want something like this:

DATE        a   b   c   d
2010-04-05  8   7   6   5
2010-04-06  9   8   7   6
2010-04-07  10  9   8   7
aronsini
  • 29
  • 6

1 Answers1

0

Is it just a,b,c,d without much chance of changing\adding new IDs?

Then something like this would do the job

SELECT DATE, 
    SUM(CASE WHEN ID = 'a' THEN VALUE ELSE 0 END) AS 'a',
    SUM(CASE WHEN ID = 'b' THEN VALUE ELSE 0 END) AS 'b',
    SUM(CASE WHEN ID = 'c' THEN VALUE ELSE 0 END) AS 'c',
    SUM(CASE WHEN ID = 'd' THEN VALUE ELSE 0 END) AS 'd'
FROM <TABLE NAME>
GROUP BY DATE

A Generic solution seems to require Dynamic SQL

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ID) 
                from <SOMETABLE>
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT DATE, ' + @cols + ' from 
         (
            select DATE, ID, VALUE
            from <SOMETABLE>
        ) x
        pivot 
        (
            sum(VALUE)
            for ID in (' + @cols + ')
        ) p '

execute(@query)

Copied from SQL Server 2005 Pivot on Unknown Number of Columns User: https://stackoverflow.com/users/426671/taryn

Gerard Woods
  • 16
  • 3