Monday, March 26, 2012

Dynamic pivot of resultset

Hi,
I need to pivot and sum a resultset (based on a particular column) but I
don't know how many cols the pivot would result in.
Say you got resultset:
[C0] [C1] [C2]
1 100 OO
1 200 KK
2 245 OO
2 244 OO
3 188 KK
4 124 AR
- would result in:
[C0] [OO] [KK] [AR]
1 100 200 0
2 489
3 188
4 124
- only, I don't know how many distinct [C1]'s there are in the resultset
Unfortunately this has to be executed on SQL2000 (with new functions in
SQL2005 it would be much easier)
Thanks in advance
Kr. SorenCheck this out:
http://www.sqlservercentral.com/scr...butions/931.asp|||Sorry,

> - only, I don't know how many distinct [C1]'s there are in the resultset
Just noticed an error in above line - [C1] should be [C2] :)
Kr. Soren|||If you don't know the number of columns to be pivoted, then even SQL 2005
won't be much help.
This solution by Itzik Ben-Gan might help you:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML
http://milambda.blogspot.com/|||Sure it will, I could then do it in the CLR!
- for what i'm doing this would also be far more efficiant :)
Soren
"ML" <ML@.discussions.microsoft.com> skrev i en meddelelse
news:5F8E3B65-B229-45D7-8EDA-D628797D852E@.microsoft.com...
> If you don't know the number of columns to be pivoted, then even SQL 2005
> won't be much help.
> This solution by Itzik Ben-Gan might help you:
> http://www.windowsitpro.com/Article...15608.html?Ad=1
>
> ML
> --
> http://milambda.blogspot.com/|||Ever hear of RAC? :)
www.rac4sql.net
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:uLsbnqkAGHA.2620@.tk2msftngp13.phx.gbl...
> Hi,
> I need to pivot and sum a resultset (based on a particular column) but I
> don't know how many cols the pivot would result in.
> Say you got resultset:
> [C0] [C1] [C2]
> 1 100 OO
> 1 200 KK
> 2 245 OO
> 2 244 OO
> 3 188 KK
> 4 124 AR
> - would result in:
> [C0] [OO] [KK] [AR]
> 1 100 200 0
> 2 489
> 3 188
> 4 124
> - only, I don't know how many distinct [C1]'s there are in the resultset
> Unfortunately this has to be executed on SQL2000 (with new functions in
> SQL2005 it would be much easier)
> Thanks in advance
> Kr. Soren
>

No comments:

Post a Comment