PIVOT

Found these pages quite interesting but not managed to make use of it yet:

• http://geekswithblogs.net/veerji/articles/45464.aspx
• http://www.tsqltutorials.com/pivot.php

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.

eg:

select *

from

(

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

pivot

(

sum(doublevalue)

for [displayname] in ([Low Price], [High Price])

) as alias