I have following kind of table:
ID | Key | Value
-- --- -----
1 A aa
2 B bb
3 A ay
4 C cc
5 B bx
6 C ct
I need the output :
A B C
--- --- ---
aa bb cc
ay bx ct
When I use PIVOT with subquery it does not work:
Select * from (Select Key, Value, Id from tableName
pivot (max(Value) for Key IN (SELECT distinct Key from tableName)));
If I use PIVOT xml, the subquery works:
Select * from (Select Key, Value, Id from tableName)
pivot xml (max(Value) for Key IN (SELECT distinct Key from tableName));
but when using PIVOT xml I get the output in xml form:
A B C
--- --- ---
<PivotSet><item...aa ... ...
How can this be converted to tabular output without the xml tags? Any help will be appreciated. Thanks!
