Given the following 2 tables
TableA
|ParameterId|ParameterName| |-----------|-------------| |0 |Param_A | |1 |Param_B | |2 |Param_C | |3 |Param_D |
TableB
|LogDateTime |ParameterId|ParameterValue| |-------------------|-----------|--------------| |2019-01-29 00:00:12|0 |4 | |2019-01-29 00:00:14|1 |2 | |2019-01-29 00:00:17|2 |0 | |2019-01-29 00:00:21|3 |1 | |2019-01-30 00:01:13|0 |7 | |2019-01-30 00:01:17|1 |3 | |2019-01-30 00:01:22|2 |5 | |2019-01-30 00:01:23|3 |9 | |2019-01-31 00:02:20|0 |3 | |2019-01-31 00:02:33|1 |0 | |2019-01-31 00:02:41|2 |1 | |2019-01-31 00:02:41|3 |6 |
How can I group the data in TableB by date, hour & minute and join with TableA to get a result as below:
|LogDateTime |Param_A|Param_B|Param_C|Param_D| |----------------|-------|-------|-------|-------| |2019-01-29 00:00|4 |2 |0 |1 | |2019-01-30 00:01|7 |3 |5 |9 | |2019-01-31 00:02|3 |0 |1 |6 |
Advertisement
Answer
You can use PIVOT
to get the desired output like following query.
;WITH cte1 AS (SELECT B.*,P.parametername FROM @tableB B INNER JOIN @tableA P ON B.parameterid = P.parameterid) SELECT * FROM (SELECT Cast(logdatetime AS SMALLDATETIME) AS logdatetime, parametervalue, parametername FROM cte1) AS SourceTable PIVOT ( SUM(parametervalue) FOR parametername IN ([Param_A],[Param_B], [Param_C],[Param_D] )) T
Note: If you want to ROUND
the minutes when excluding the seconds and millisecond in that case you should do like following..
CAST(logdatetime AS smalldatetime)
If you simply wants to truncate the seconds and milliseconds, you can change it like following.
CAST(DateAdd(minute, DateDiff(minute, 0, logdatetime), 0) AS smalldatetime)
If your TableA
values are not fixed (can vary in future), in that case you need to go for a dynamic PIVOT
.
Another simpler solution will be like following query using CASE WHEN
if your Parameter are fixed.
;WITH cte AS (SELECT Cast(logdatetime AS SMALLDATETIME) AS DT, CASE WHEN parameterid = 0 THEN parametervalue END AS 'Param_A', CASE WHEN parameterid = 1 THEN parametervalue END AS 'Param_B', CASE WHEN parameterid = 2 THEN parametervalue END AS 'Param_C', CASE WHEN parameterid = 3 THEN parametervalue END AS 'Param_D' FROM @TableB) SELECT dt AS LogDateTime, Isnull(Sum(param_a), 0) AS Param_A, Isnull(Sum(param_b), 0) AS Param_B, Isnull(Sum(param_c), 0) AS Param_C, Isnull(Sum(param_d), 0) AS Param_D FROM cte GROUP BY dt