PIVOT SQL Table By Date, Name And Value

I have a query that returns me data where the channel name exists in another table so I only get back a specific set of channels. SELECT DISTINCT ReadingDate, SerialNumber, Ch

Solution 1:

You can try to use cte recursion make a time calendar table, then make row number by the time.

then use dynamic SQL with pivot to make it.

Here is a sample for you.

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);


;WITH CTE AS (
    SELECT CAST('00:00' AS TIME) startDt, CAST('23:45' AS TIME) endDt
    UNION ALL 
    SELECT DATEADD(MINUTE, 15, startDt),endDt
    FROM CTE
    WHERE DATEADD(MINUTE, 15, startDt) <endDt
), TimeTable AS (
    select *,ROW_NUMBER() OVER (ORDER BY startDt) rn 
    FROM (
       SELECT  startDt,endDt
       FROM CTE
       UNION ALL 
       SELECT CAST('23:45' AS TIME) startDt, CAST('23:45' AS TIME) endDt
    ) t1
)



select @cols = CONCAT(@cols,'MAX(CASE WHEN '''+CAST(startDt AS VARCHAR(5))+''' = CAST(ReadingDate AS TIME) THEN ChannelValue ELSE 0 end) AS ',QUOTENAME(CAST(startDt AS VARCHAR(5))),', ')
from TimeTable
WHERE startDt <= endDt
ORDER BY rn 

SET @cols = left(@cols, len(@cols) - 1)


set @query = '
;WITH CTE AS ( SELECT CAST('''+ cast('00:00' as varchar(5))+''' AS TIME) startDt, CAST('''+ cast('23:45' as varchar(5))+''' AS TIME) endDt
    UNION ALL 
    SELECT DATEADD(MINUTE, 15, startDt),endDt
    FROM CTE
    WHERE DATEADD(MINUTE, 15, startDt) <endDt
), TimeTable AS (
    select *,ROW_NUMBER() OVER (ORDER BY startDt) rn 
    FROM (
       SELECT  startDt,endDt
       FROM CTE
       UNION ALL 
       SELECT CAST('''+ cast('23:45' as varchar(5))+''' AS TIME) startDt, CAST('''+ cast('23:45' as varchar(5))+''' AS TIME) endDt
    ) t1
)

SELECT CONVERT(VARCHAR(10),ReadingDate, 126),
      SerialNumber,
      ChannelName,
       '+ @cols +'
FROM  T
GROUP BY CONVERT(VARCHAR(10),ReadingDate, 126) ,SerialNumber,ChannelName
'

exec(@query)

sqlfiddle

Solution 2:

You can also achieve the same result using PIVOT construct as follows:

SELECT [Serial], [Channel], [ReadingDate], [00:15], [00:30], ....
FROM(
    SELECT 
        SerialNumber AS [Serial],
        ChannelName AS [Channel],
        CAST(ReadingDate AS DATE) AS [ReadingDate],
        CAST(ReadingDate AS TIME(0)) AS [ReadingTime],
        ChannelValue AS [Value]
    FROM [Staging].[UriData]
    WHERE
        ChannelName IN (SELECT ChannelName FROM Staging.ActiveChannels )
        AND Processed = 0
    ) AS [Raw]
PIVOT
(
    MAX( [Value] ) FOR [ReadingTime] IN( [00:15], [00:30], .... )
) AS pvt
ORDER BY ReadingDate DESC, Channel, [Serial]

To generate "Time" columns (and save you from having to type manually) run the below query:

DECLARE @cols AS NVARCHAR(MAX)
;WITH Times AS (
    SELECT CAST( '00:00' AS TIME) AS [Time]
    UNION ALL 
    SELECT DATEADD( MINUTE, 15, [Time] )
    FROM Times
    WHERE [Time] < CAST( '23:45' AS TIME )
)
--SELECT * FROM Times
SELECT @cols = CONCAT( @cols, QUOTENAME( CAST( [Time] AS VARCHAR( 5 ) )), ', ' )
FROM Times

SET @cols = LEFT( @cols, LEN( @cols ) - 1 )
PRINT @cols

Copy/paste the result into the "PIVOT" query above.