dynamic pivoting

[brucegonewild]

hey there,
I need to turn this following table:

Col1 |Col2 |Col3
-----|-----|----
r11  |row12|row13
-----|-----|----
r21  |row22|row23
-----|-----|----

into:

Col1 | r12 | r22 
-----|-----|----

Naturally, I would have to use "PIVOT". Except, when using the pivot functionality I have to statically include the values that will turn into the col-header. My problem is, these rows that are being pivoted, are dynamic!! I have no way of knowing what they are and therefore
Pivot (Max(Col1) for Col2 in ([r12], [r22]) will not work. I tried putting a Select * inside the "IN" section... that didn't work...

Your help is much apreciated

 

[Qik3Coder]

As "ickey" as it may be, you may have to gen dynamic sql and execute it.

-Sometimes the answer to your question is the hack that works

 

[TysonLPrice]

I don't know if this helps since it's not dynamic but it might get you started. It was submitted by chamilz to help me out recently:

create table #test
(plant varchar(3),
 shoulder tinyint,
 elbow tinyint,
 lowback tinyint,
 carpaltunnel tinyint)

insert #test values('AEP',0,0,1,1)
insert #test values('AEP',1,0,1,0)
insert #test values('MAP',1,0,0, 0)
insert #test values('MAP',0,0,0,1)
insert #test values('ELP',0,1,1,0)
insert #test values('ELP',0,1,1,0)
insert #test values('MMP',1,0,0,1)
insert #test values('MMP',1,1,0,1)


select 'shoulder',sum(AEP) as Tot_AEP, sum(MAP) as Tot_MAP, sum(ELP) as Tot_ELP,sum(MMP) as Tot_MMP
from #test
PIVOT
(sum(shoulder)
 for plant in (AEP,MAP,ELP,MMP)) a
UNION
select 'elbow',sum(AEP) as Tot_AEP, sum(MAP) as Tot_MAP, sum(ELP) as Tot_ELP,sum(MMP) as Tot_MMP
from #test
PIVOT
(sum(elbow)
 for plant in (AEP,MAP,ELP,MMP)) b
UNION
select 'lowback',sum(AEP) as Tot_AEP, sum(MAP) as Tot_MAP, sum(ELP) as Tot_ELP,sum(MMP) as Tot_MMP
from #test
PIVOT
(sum(lowback)
 for plant in (AEP,MAP,ELP,MMP)) c
UNION
select 'carpaltunnel',sum(AEP) as Tot_AEP, sum(MAP) as Tot_MAP, sum(ELP) as Tot_ELP,sum(MMP) as Tot_MMP
from #test
PIVOT
(sum(carpaltunnel)
 for plant in (AEP,MAP,ELP,MMP)) d

 

[brucegonewild]

hey Tyson,
thanks for the quote... however, unfortunately i have just learned a very painful listen:

http://www.sqlmag.com/article/articleid/94856/94856.html

Are you reading this Uncle Bill!!! Get your slaves fixing it! Static pivoting is really useless (to me)!

cheers!