0f5d0e92   
   From: esquel@sommarskog.se   
      
   mcolson (mcolson1590@gmail.com) writes:   
   > 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'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   
      
   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@sommarskog.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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)