1

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.

Improve this question
7
  • 1
    Could show us the sample data from tables instead of query result. Commented Sep 9, 2018 at 19:34
  • Why did you want to create multiple Time columns is there any logic? Commented Sep 9, 2018 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. Commented Sep 9, 2018 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. Commented Sep 9, 2018 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? Commented Sep 9, 2018 at 19:54

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

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.
Wow, that works incredibly well. It's much more complex than I envisaged. I am no SQL expert as you can probably tell...
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..
Thank you...The answer is correct and thanks for your advice.
My answer earlier than another one. And I think my solution was more elasticities
|
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.