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
    Модератор

Все ответы

  • Try

    select * from Test PIVOT (SUM([hours]) FOR yr IN ([2010],[2009],[2008],[2007])) pvt 


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


    My blog

    6 марта 2012 г. 21:44
    Модератор
  • You need to use an aggregate function for pivot, but you can use, for example, max() to do what you want, so

    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 id, [2010], [2009], [2008], [2007]
    from test
    pivot
    (max(hours) for yr in ([2010], [2009], [2008], [2007])) pvt

    Thanks for the sample tables and data, that is always very helpful.

    Tom

    6 марта 2012 г. 21:45
  • Thanks for the quick response...that works perfectly, but for a real-world situation, I won't know the actual year values, so is it possible to modify the IN clause somehow so it is dynamic?
    6 марта 2012 г. 21:50
  • Thanks for the quick response...that works perfectly, but for a real-world situation, I won't know the actual year values, so is it possible to modify the IN clause somehow so it is dynamic?
    6 марта 2012 г. 21:53
  • 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
    Модератор
  • Example for dynamic PIVOT:

    http://www.sqlusa.com/bestpractices2008/dynamic-sql-pivot/

    SSRS has built-in dynamic PIVOT. A real riot!


    Kalman Toth SQL SERVER & BI TRAINING

    12 марта 2012 г. 19:25
    Модератор