SQL group records by date/hour/minute and join table

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      |

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 

Online Demo

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