Thursday, May 27, 2010


Found these pages quite interesting but not managed to make use of it yet:
It's about how to use PIVOT. Have a look round his site and look for the UNPIVOT pages, much geeky fun to be had within a T-SQL server (2005 onwards.)

[UPDATE] The key thing I've found (as highlighted in the first link) is that the PIVOT statement operates upon the fields returned in the FROM clause, NOT the SELECT clause. This is important because it means, if you have a JOIN or two, you probably have a lot more fields to quote in your clause than you think. The solution, I found, is to nest the original query inside the FROM( ) AS virtualtable thereby reducing the pivot to only the fields you're concerned with.


select *
distinct top 100
v.pointid, v.doublevalue, p.displayname
from [property] p
inner join pointvalue v on p.propertyid = v.propertyid
inner join point pt on v.pointid = pt.pointid
where v.pointid in (select top 5 p.pointid from point p where p.instanceid = 36132)
) virtualtable
for [displayname] in ([Low Price], [High Price])
) as alias

No comments:

Post a Comment