How to transpose/pivot time series data-CodePudding

I have data in a table that is in such form:

Day	Group	Signal	T0	T1	...	T23
2022-01-01	Voltage	L1	230.0	229.5	...	231.2
2022-01-01	Voltage	L2	225.4	231.2	...	230.3

Every day is split into 24 hours and values are stored into columns T0-T23.

How can I get to the desired result, which is this:

Date	Time	Group	Signal	SignalValue
2022-01-01	00:00	Voltage	L1	230.0
2022-01-01	01:00	Voltage	L1	229.5
...	...	...	...
2022-01-01	23:00	Voltage	L2	230.3

Thanks!

CodePudding user response：

You don't say what your DBMS is but the following UNPIVOT example will work in SQL Server, YMMV in other products

SELECT  [Day],
        CONVERT(VARCHAR(5),TIMEFROMPARTS(ROW_NUMBER() OVER (ORDER BY Day)-1,0,0,0,0),108) AS [Time],
        [Group],
        Signal,
        Val
FROM    (
            SELECT  [Day],
                    [Group],
                    Signal,
                    T0,
                    T1,
                    T23
            FROM    MyData
        ) AS p
        UNPIVOT 
        (
            Val FOR Times IN (T0,T1,T23)
        ) AS t
ORDER BY [Day],
         [Group],
         Signal