PIVOT SQL Table by Date, Name and Value

Question

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.

Could show us the sample data from tables instead of query result. — D-Shih, Sep 09 '18 at 19:34
Why did you want to create multiple `Time` columns is there any logic? — D-Shih, Sep 09 '18 at 19:35
D-Shih, Is there anywhere I can post the full data set as it has 1780 rows at the moment. I assume that is what you mean. — The OrangeGoblin, Sep 09 '18 at 19:37
Regarding the time, the date format is '2018-09-09 20:00:00'. There will be 96 readings a day per ChannelName. So I need to get the first reading at 00:15 in the first time column and then the last reading at midnight of that same day, with all of the other times in between e.g. 00:15, 00:30, 00:45 etc. — The OrangeGoblin, Sep 09 '18 at 19:40
If you know that there are 96 "readings" per day, serial and channel, starting at 00:00 and ending at 23:45, for what reason do you store this Information in a database table? — Wolfgang Kais, Sep 09 '18 at 19:54
It needs to be converted into a table that can eventually be exported in a flat file. — The OrangeGoblin, Sep 09 '18 at 19:57
You don't need to post the whole thing, just some examples and the data you expect to get for those examples. — Thom A, Sep 09 '18 at 19:57

D-Shih · Accepted Answer · 2018-09-22T18:26:33.040

3

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

edited Sep 22 '18 at 18:26

answered Sep 09 '18 at 19:57

D-Shih

44,943
6
31
51

That's kind of what I am looking for. I have edited the question as I made an error in the required output. I require the values to be under the name column. – The OrangeGoblin Sep 09 '18 at 20:16
Wow, that works incredibly well. It's much more complex than I envisaged. I am no SQL expert as you can probably tell... – The OrangeGoblin Sep 09 '18 at 20:38
If you don't want that so complex, you can just use simple pivot but there will be a lot of thing need to do.. – D-Shih Sep 09 '18 at 20:42
1

Thank you...The answer is correct and thanks for your advice. – The OrangeGoblin Sep 09 '18 at 20:45
My answer earlier than another one. And I think my solution was more elasticities – D-Shih Sep 22 '18 at 15:45
The answer was correct, I have re-ticked it, sorry, I needed someone to explain it to me. I am not great with SQL. Although I am having a problem where the dates and times aren't coming out correctly. I am going to post another question relating to that... – The OrangeGoblin Sep 22 '18 at 15:45
Ok maybe you could create another thread question I will take a look it – D-Shih Sep 22 '18 at 15:48
https://stackoverflow.com/questions/52458362/first-value-of-tomorrow-to-appear-on-last-cell-of-todays-row – The OrangeGoblin Sep 22 '18 at 15:57
Thank you again for taking the time to help me. – The OrangeGoblin Sep 22 '18 at 15:57
Ok I am outside now. When I go back my home I will check it out – D-Shih Sep 22 '18 at 16:11

score 2 · Answer 2 · answered Sep 21 '18 at 00:52

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.

PIVOT SQL Table by Date, Name and Value

2 Answers2

Linked