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, ChannelName, ChannelValue
FROM
[Staging].[UriData]
WHERE
ChannelName IN (SELECT ChannelName FROM [dbo].[Channels])
ORDER BY
ReadingDate DESC, ChannelName
The importance of this query is it only brings back channels that are storing moving data and not fixed diagnostic data.
Results In (snippet):
ReadingDate | SerialNumber | ChannelName | ChannelValue
2018-09-09 20:30:00 2209 m1 461
2018-09-09 20:30:00 2209 m2 0
2018-09-09 20:30:00 2209 m3 50
2018-09-09 20:30:00 2209 m4 15631
2018-09-09 20:30:00 2209 m5 1
2018-09-09 20:30:00 2209 m6 8150
2018-09-09 20:30:00 2209 m7 0
2018-09-09 20:30:00 2209 m8 2790
2018-09-09 20:30:00 2209 m9 0
2018-09-09 20:15:00 2209 m1 2452
2018-09-09 20:15:00 2209 m2 0
2018-09-09 20:15:00 2209 m3 50
2018-09-09 20:15:00 2209 m4 15629
2018-09-09 20:15:00 2209 m5 1
2018-09-09 20:15:00 2209 m6 8100
2018-09-09 20:15:00 2209 m7 0
2018-09-09 20:15:00 2209 m8 2780
I then want to pivot this data into columns so they are grouped by the day (date), then the time is taken from that date.
Required output:
DATE | SERIAL | ChannelName | 00:15 | 00:30 | ..... | 23:45
2018-09-06 2209 m1 Value Value ..... Value
2018-09-06 2209 m2 Value Value ..... Value
I have been playing around with pivots but I am not getting the data in the format I need as described.
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
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.
