Mystery of PIVOT, or is it???

[eo]

Hi,

I think this is quite unique, but there is hopefully a way to achieve this.

My data has 2 identifyers and 3 values:

state   city       value1   value2   value3
SA      Adealide   100      50       60
SA      Port Adl   20       50       20
WA      Perth      15       20       10
WA      Fremantle  25       80       60

I actually need to use the value labels as slicers so need the data as

State  City     KPI     Value
SA     Adelaide Value1  100
SA     Adelaide Value2   50
SA     Adelaide Value3   60
SA     Port Adl Valie1   20
....

Is this at all possible?

Many thanks,
EO

EO
Hertfordshire, England

 

[drlex]

On the data given, I'd use UNION ALL

SELECT [State], [City], 'Value1', [value1] from TABLE
UNION ALL
SELECT [State], [City], 'Value2', [value2] from TABLE
UNION ALL
SELECT [State], [City], 'Value3', [value3] from TABLE
ORDER by 1,2,3

soi là, soi carré

 

[markros]

SELECT * from yourTable UNPIVOT (Value for KPI IN ([Value1],[Value2],[Value3])) unpvt

PluralSight Learning Library

 

[drlex]

UNPIVOT not available in SS 2000, but I guess that that version is now quite rare.

soi là, soi carré