# dynamic columns with pivot?...or not Pivot?

• ### 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

• 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 Thursday, March 29, 2012 9:07 AM
Friday, March 23, 2012 1:27 PM

### All replies

• Friday, March 23, 2012 11:11 AM
• 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 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