Need to Pivot some data

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

CK Bhatia Say Hey Kid Points: 679 More actions · Answer 1

If the number of values for s1 is fixed (in your example - 3) - then you can use the following

create table #temp (s1 tinyint, s2 tinyint, crit float)

insert into #temp select 1, 3, 9.0

insert into #temp select 2, 3, 8.0

insert into #temp select 3, 3, 7.0

insert into #temp select 1, 5, 1.0

insert into #temp select 2, 5, 2.0

insert into #temp select 3, 5, 3.0

select s2,

(select crit from #temp t2 where t2.s2 = t1.s2 and t2.s1 = 1) as Crit1,

(select crit from #temp t2 where t2.s2 = t1.s2 and t2.s1 = 2) as Crit2,

(select crit from #temp t2 where t2.s2 = t1.s2 and t2.s1 = 3) as Crit3

from #temp t1

group by s2

Cheers

I feel the need - the need for speed

CK Bhatia

Jason- SSCrazy Points: 2764 More actions · Answer 2

This posted script from iecdba should do the trick...

-

RyanRandall SSChampion Points: 13623 More actions · Answer 3

CK Bhatia - If you feel the need for speed, you should use this query instead:

select s2,

sum(case when s1 = 1 then crit else 0 end) as Crit1,

sum(case when s1 = 2 then crit else 0 end) as Crit2,

sum(case when s1 = 3 then crit else 0 end) as Crit3

from #temp t1

group by s2

Alberto - If you only need a static pivot, use the query above. If you need a dynamic piovt, these are the references I usually give:

I've not compared these to the reference Jason posted, but all are generally the same idea.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.