locked
dynamic columns with pivot?...or not Pivot? RRS feed

  • Question

  • I have a result in a table like below:

    PersonNumber   Score
    1  20
    1  25
    2  21
    2  23
    2  27
    2  30
    3  26
    3  27
    3  28

    And I want the result like this:

    PersonNumber   Score1     Score2    Score3   Score4 
    1                       20           25          null        null      
    2                       21           23          27          30
    3                       26           27          28          null

    As you can see, the total rows per person can be variable with a maximum of 4.

    I realy don't have a idea how to solve this problem,

    Can anyone help?

    Thanks.


    Friday, March 23, 2012 11:04 AM

Answers

  • declare @t table(PersonNumber  int,Score int)
    insert into @t
    select 1 , 20  union all
    select 1,  25     union all
    select 2,  21      union all
    select 2 , 23  union all
    select 2 , 27   union all
    select 2,  30    union all
    select 3 , 26    union all
    select 3,  27    union all
    select 3,  28


    ;with cte as
    (
     select *,'score'+cast(row_number()over(partition by personnumber order by score) as varchar(10)) as r from @t
    )


    select PersonNumber ,max(case when r='score1' then Score end) as score1,
    max(case when r='score2' then Score    end)score2    ,
    max(case when r='score3' then Score     end)score3,
    max(case when r='score4' then Score end) score4 from cte
    group by PersonNumber

    Thanks and regards, Rishabh , Microsoft Community Contributor

    Friday, March 23, 2012 11:14 AM
  • select * from (select PersonNumber, Score, 
    'Score' + cast(row_number() over (partition by PersonNumber order by Score) as varchar(10)) as Rn 
    FROM PersonScores) X
    PIVOT (max(Score) FOR Rn IN ([Score1],[Score2],[Score3],[Score4])) pvt 


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


    My blog

    • Marked as answer by KJian_ Thursday, March 29, 2012 9:07 AM
    Friday, March 23, 2012 1:27 PM

All replies

  • declare @t table(PersonNumber  int,Score int)
    insert into @t
    select 1 , 20  union all
    select 1,  25     union all
    select 2,  21      union all
    select 2 , 23  union all
    select 2 , 27   union all
    select 2,  30    union all
    select 3 , 26    union all
    select 3,  27    union all
    select 3,  28


    ;with cte as
    (
     select *,'score'+cast(row_number()over(partition by personnumber order by score) as varchar(10)) as r from @t
    )


    select PersonNumber ,max(case when r='score1' then Score end) as score1,
    max(case when r='score2' then Score    end)score2    ,
    max(case when r='score3' then Score     end)score3,
    max(case when r='score4' then Score end) score4 from cte
    group by PersonNumber

    Thanks and regards, Rishabh , Microsoft Community Contributor

    Friday, March 23, 2012 11:14 AM
  • Check Rishabh's reply if you don't want to use PIVOT and your columns are fixed, by assuming max such cols.

    In case of dynamic Cols check following blog post on creating Dynamic Pivot: http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011

    Friday, March 23, 2012 11:18 AM
  • Great, this is what i needed,

    thanks all :)

    Friday, March 23, 2012 1:16 PM
  • select * from (select PersonNumber, Score, 
    'Score' + cast(row_number() over (partition by PersonNumber order by Score) as varchar(10)) as Rn 
    FROM PersonScores) X
    PIVOT (max(Score) FOR Rn IN ([Score1],[Score2],[Score3],[Score4])) pvt 


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


    My blog

    • Marked as answer by KJian_ Thursday, March 29, 2012 9:07 AM
    Friday, March 23, 2012 1:27 PM
  • @Naomi-

    You are amazing!!!

    Friday, March 23, 2012 6:09 PM