I am attempting to convert table1 to the output shown in table two. Is there a way to use multiple unpivot to accomplish this without skewing the data. Not sure what I'm missing but I'm getting 4 entries for each readdatetme.
I also tried grouping to no avail.
SELECT METERNUM
,SMREADDATE
,SMREADAMOUNT
FROM SMARTMETER A
UNPIVOT
(
SMREADDATE
FOR READDATE IN (READDATETME1,READDATETME2)
) B
UNPIVOT
(
SMREADAMOUNT
FOR READAMOUNT IN (READAMOUNT1,READAMOUNT2)
) C
SMARTMETER
METERNUM | READDATETME1 | READAMOUNT1 | READDATETME2 | READAMOUNT2 |
---|---|---|---|---|
0001 | 20240501 01:00 | 100 | 20240501 02:00 | 120 |
0001 | 20240502 01:00 | 110 | 20240502 02:00 | 115 |
T2
METERNUM | READDATETME | READAMOUNT |
---|---|---|
0001 | 20240501 01:00 | 100 |
0001 | 20240501 02:00 | 120 |
0001 | 20240502 01:00 | 110 |
0001 | 20240502 02:00 | 115 |
CROSS APPLY
can be used as a makeshift multi-UNPIVOT
:
SELECT s.METERNUM, x.READDATETME, x.READAMOUNT
FROM SMARTMETER AS s
CROSS APPLY
(
VALUES(READDATETME1, READAMOUNT1),
(READDATETME2, READAMOUNT2)
) AS x(READDATETME, READAMOUNT)
ORDER BY s.METERNUM, x.READDATETME;
Results:
METERNUM | READDATETME | READAMOUNT |
---|---|---|
1 | 2024-05-01 01:00:00.000 | 100 |
1 | 2024-05-01 02:00:00.000 | 120 |
1 | 2024-05-02 01:00:00.000 | 110 |
1 | 2024-05-02 02:00:00.000 | 115 |
Also really annoying for READDATETME
to be missing an I
, for what it's worth.