none
PIVOT help, need PIVOT without aggregation

    Вопрос

  • Using the dml and sql below, the test table is created.  I am not clear on Pivot, can someone show how to get the following result:

    id  2010  2009  2008  2007

    1   1000  2000  3000  4000

    2   5000  6000  7000  8000

    create table test (
    id int,
    yr int,
    hours int
    )

    insert into test select 1,2010,1000
    insert into test select 1,2009,2000
    insert into test select 1,2008,3000
    insert into test select 1,2007,4000
    insert into test select 2,2010,5000
    insert into test select 2,2009,6000
    insert into test select 2,2008,7000
    insert into test select 2,2007,8000
    go

    select * from test

    id          yr          hours
    ----------- ----------- -----------
    1           2010        1000
    1           2009        2000
    1           2008        3000
    1           2007        4000
    2           2010        5000
    2           2009        6000
    2           2008        7000
    2           2007        8000

    6 марта 2012 г. 21:39

Ответы

  • It is possible using dynamic pivot.

    DECLARE @SQL NVARCHAR(MAX), @Cols NVARCHAR(MAX)
    
    SELECT @Cols = STUFF((SELECT ', ' + QUOTENAME(yr) FROM (SELECT DISTINCT yr FROM Test) T
    ORDER BY yr DESC FOR XML PATH('')),1,2,'')
    SET @SQL = 
    'select * from Test PIVOT (SUM([hours]) FOR yr IN (' +@Cols + ')) pvt'
    
    EXECUTE (@SQL) 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Помечено в качестве ответа KJian_ 13 марта 2012 г. 3:09
    6 марта 2012 г. 22:02

Все ответы