CODEWITHSUNDEEP
sql-serverpivot
Santi Peñate-Vera
Santi Peñate-Vera

Reputation: 1186

Average values given times and make a pivot table

I have a table like this:

time     | sensor | value |
---------------------------
10:00:00 | 1      |  12.3 |
10:00:00 | 2      |  14.3 |
10:00:00 | 3      |  15.3 |
10:00:01 | 1      |  15.3 |
10:00:02 | 2      |  11.3 |
10:00:01 | 3      |  10.3 |
10:00:03 | 1      |   9.3 |
10:00:03 | 2      |  15.3 |
10:00:04 | 3      |  11.3 |
...
10:05:01 | 1      |  15.3 |
10:05:02 | 2      |  11.3 |
10:05:01 | 3      |  10.3 |
10:05:03 | 1      |   9.3 |
10:05:03 | 2      |  15.3 |
10:05:04 | 3      |  11.3 |

And I need a Pivot table where the measurements are averaged in time intervals of lets say one minute, and ordered by sensor:

time     | sensor 1| sensor 2| sensor 3|
----------------------------------------
10:00:00 |   11    |    12   |    10   |
10:01:00 |   10    |    13   |    15   |
...

I learned how to do this in MySQL, but now I am forced to use SQL Server and apparently this is completely different to MySQL.

Any help is appreciated, also some non trivial tutorial on this would be helpful.

EDIT

Attempt after the reply from Giorgi Nakeuri:

DECLARE @cols VARCHAR(MAX)

SELECT STUFF((SELECT '],[' + CAST([Group] AS VARCHAR(10)) FROM tblGroups
GROUP BY [Group]
FOR XML PATH('')), 1, 2, '') + ']'

DECLARE @s VARCHAR(MAX) = 'with cte as(
    SELECT tblData.Timestmp, tblSeries.GroupID as SensorID, tblData.SeriesID, tblData.DataValue
    FROM tblData 
    INNER JOIN tblSeries ON tblData.SeriesID = tblSeries.SeriesID

    WHERE (tblSeries.[SeriesName] = ' + 'ActivePower' 
            + 'AND tblData.Timestmp > ' + '2015-02-05 00:00:00' + ' AND  tblData.Timestmp < ' + '2015-02-05 23:59:59'
            + 'AND tblData.DataValue>100)
)
select * from cte
pivot(AVG(DataValue) for GroupID in(' + @cols + ' )) p'


EXEC(@s)

The result is:

[150],[151],[152],[154],[159],[160],[164],[165],[166],[167],[168],[169],[171],[172],[173],[174],[175],[176],[180],[181],[182],[184],[185],[186],[187],[188],[189],[191],[192],[193],[194],[195],[196],[197],[20],[201],[205],[206],[207],[208],[209],[21],[210],[217],[218],[219],[220],[221],[222],[223],[224],[225],[226],[227],[228],[229],[231],[232],[233],[236],[237],[249],[251],[252],[253],[254],[258],[259],[260],[262],[263],[276],[277],[278],[279],[281],[288],[289],[290],[291],[293],[294],[295],[296],[300],[301],[302],[304],[308],[309],[314],[315],[316],[317],[324],[326],[329],[330],[331],[332],[333],[334],[335],[339],[340],[344],[347],[348],[351],[352],[353],[354],[355],[356],[357],[359],[370],[372],[373],[374],[375],[376],[380],[381],[382],[383],[384],[385],[386],[387],[388],[389],[390],[394],[395],[396],[397],[398],[400],[404],[405],[406],[407],[408],[409],[410],[411],[412],[413],[414],[418],[419],[420],[421],[425],[432],[435],[436],[437],[438],[439],[443],[444],[445],[452],[453],[457],[465],[466],[467],[468],[484],[485],[486],[487],[494],[495],[498],[499],[501],[506],[507],[511],[515],[516],[520],[521],[523],[527],[530],[531],[532],[533],[548],[550],[601],[605],[614],[615],[617],[81],[82],[829]

This are the sensor ID's, but not the Pivot table...

Upvotes: 1

Views: 36

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try this:

select * from someTable
pivot(AVG(value) for sensor in([1],[2],[3])) p

If you have more columns in your table that you have showed then:

with cte as(select time, sensor, value from someTable)
select * from cte
pivot(AVG(value) for sensor in([1],[2],[3])) p

Dynamic version:

DECLARE @cols VARCHAR(MAX) = '' 

SELECT @cols = STUFF((SELECT '],[' + CAST([Group] AS VARCHAR(10)) FROM tblGroups 
WHERE [Group] <> 'meteo' 
GROUP BY [Group] 
FOR XML PATH('')), 1, 2, '') + ']' 

DECLARE @s VARCHAR(MAX) = 'with cte as( 
SELECT DATEADD(mi, datediff(mi, 0, tblData.Timestmp), 0) Timestmp, tblSeries.GroupID as SensorID, tblData.DataValue 
FROM tblData INNER JOIN tblSeries ON tblData.SeriesID = tblSeries.SeriesID 
WHERE tblSeries.[SeriesName] = ' + '''ActivePower''' + ' AND tblData.Timestmp > ''' + '2015-02-05 00:00:00''' + ' 
AND tblData.Timestmp < ' + '''2015-02-05 23:59:59''' + ' AND tblData.DataValue>100 
) 
select * from cte 
pivot(AVG(DataValue) for GroupID in(' + @cols + ' )) p' 

EXEC( @s)

Upvotes: 1

Related Questions