How to make awkward pivot of sql table in SQL Server 2005?
Posted
by Oliver
on Stack Overflow
See other posts from Stack Overflow
or by Oliver
Published on 2010-03-12T14:56:47Z
Indexed on
2010/03/13
11:05 UTC
Read the original article
Hit count: 300
I have to rotate a given table from an SQL Server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format?
Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time.
Given Data
ID | Label | Numerator | Denominator | Ratio
---+-----------------+-------------+---------------+--------
1 | LabelNameOne | 41 | 10 | 4,1
1 | LabelNameTwo | 0 | 0 | 0
1 | LabelNameThree | 21 | 10 | 2,1
1 | LabelNameFour | 15 | 10 | 1,5
2 | LabelNameOne | 19 | 19 | 1
2 | LabelNameTwo | 0 | 0 | 0
2 | LabelNameThree | 15 | 16 | 0,9375
2 | LabelNameFive | 19 | 19 | 1
2 | LabelNameSix | 17 | 17 | 1
3 | LabelNameOne | 12 | 12 | 1
3 | LabelNameTwo | 0 | 0 | 0
3 | LabelNameThree | 11 | 12 | 0,9167
3 | LabelNameFour | 12 | 12 | 1
3 | LabelNameSix | 0 | 1 | 0
Wanted result
ID | ValueType | LabelNameOne | LabelNameTwo | LabelNameThree | LabelNameFour | LabelNameFive | LabelNameSix
---+-------------+--------------+--------------+----------------+---------------+---------------+--------------
1 | Numerator | 41 | 0 | 21 | 15 | |
1 | Denominator | 10 | 0 | 10 | 10 | |
1 | Ratio | 4,1 | 0 | 2,1 | 1,5 | |
2 | Numerator | 19 | 0 | 15 | | 19 | 17
2 | Denominator | 19 | 0 | 16 | | 19 | 17
2 | Ratio | 1 | 0 | 0,9375 | | 1 | 1
3 | Numerator | 12 | 0 | 11 | 12 | | 0
3 | Denominator | 12 | 0 | 12 | 12 | | 1
3 | Ratio | 1 | 0 | 0,9167 | 1 | | 0
© Stack Overflow or respective owner
