I have data that has been captured in historian. I'm trying to create
a view that displays data captured via a Historian in a Pivot Table.
I'm having trouble with using the PIVOT operator. Any help on how
this could be turned into a pivot table?

SELECT DateTime, TagName, Value AS Pressure
FROM History
WHERE (DateTime >= CONVERT(DATETIME, '2010-12-22 00:00:00', 102))
AND (TagName IN ('Rough', 'HiVac1', 'Leg1', 'Leg2')

'What I get
2010/12/23 Rough 2.100
2010/12/23 HiVac1 1433.59
2010/12/23 Leg1 1536
2010/12/23 Leg2 0.40
2010/12/24 Rough 2.100
2010/12/24 HiVac1 3276.80
2010/12/24 Leg1 3379
2010/12/24 Leg2 2.10

'What I'm trying to get
Rough HiVac1 Leg1 Leg2
2010/12/23 2.100 1433.59 1536 0.40
2010/12/24 2.100 3276.80 3379 2.10

Thanks in advance for any help.

mcolson wrote:
What version of SQL Server? If pre-2005, google for SQL pivot. If 2005+.
look for the pivot topics in SQL BOL (Books Online)

mcolson (mcolson1590 (AT) gmail (DOT) com) writes:
SELECT DateTime,
MAX(CASE TagName WHEN 'Rough' THEN Value END) AS Rough,
MAX(CASE TagName WHEN 'HiVac1' THEN Value END) AS HiVac1,
MAX(CASE TagName WHEN 'Leg1' THEN Value END) AS Leg1,
MAX(CASE TagName WHEN 'Leg2' THEN Value END) AS Leg2
FROM History
WHERE DateTime >= '20101222'
GROUP BY DateTime

You may note that I don't actually use the PIVOT operator. I think
the above works just as well, and it runs about any DBMS. I have not
even bothered to learn the PIVOT syntax.

Note also the comparison on the date value. The format YYYYMMDD is always
interpreted the same, so this is a safe format.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx