SQLTeam.com | Weblogs | Forums

Create pivot table

sql-server-2014

#1

I have a logger table with timestamp,tagname and tagvalue fields.
Every time tag value changes, the control system writes record to the table with those 3 parameters.
I want to run a pivot table query to get all data for 3 different tags to show the values of those 3 tags,
When I run the query below, I get in return a dataset with all timestamp records in the table and lots of null values in the value fields(the SQL returns me all timestamp values).
I use the query:
SELECT * FROM (SELECT [timestamp],[_VAL] AS '_VAL',[point_id] FROM DATA_LOG) p PIVOT(SUM([_VAL]) FOR point_id in ([GG02.PV_CURNT],[GG02.PV_JACKT],[GG02.PV_SPEED],[GG02.PV_TEMP])) as tagvalue ORDER BY timestamp ASC
Please anybody can help me how to limit the timestamp that SQL returns me only for timestamp relevant to those 3 tags and not all timestamp values in the table?


#2

Please post Create table statements for the tables involved and insert statements with sample data and the output you want.


#3

what is the data type of point_id

SELECT * 
  FROM (
SELECT [timestamp],[_VAL] AS '_VAL',[point_id] FROM DATA_LOG
where point_id in (
'GG02.PV_CURNT','GG02.PV_JACKT','GG02.PV_SPEED','GG02.PV_TEMP'
)
) p PIVOT(SUM([_VAL]) FOR point_id in (
[GG02.PV_CURNT],[GG02.PV_JACKT],[GG02.PV_SPEED],[GG02.PV_TEMP])
) as tagvalue ORDER BY timestamp ASC