Wednesday, April 1, 2009

SQL: 24hr Pivot

this is a little sample of how to pivot data in the form of
names|values|hours
a|1|12
a|9.1|13
b|2.5|12
b|3.6|13

and change it to:
names|12|13
a|1|9.1
b|2.5|3.6

SELECT * FROM (
SELECT names,Hours, Values
FROM aTable a
WHERE a.names IN ('a','b')
) p

PIVOT (
SUM(values) FOR [hours] IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) AS pvt

No comments: