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.
SELECT METERNUM
,SMREADDATE
,SMREADAMOUNT
FROM SMARTMETER A
UNPIVOT
(
SMREADDATE
FOR READDATE IN (READDATETME1,READDATETME2)
) B
UNPIVOT
(
SMREADAMOUNT
FOR READAMOUNT IN (READAMOUNT1,READAMOUNT2)
) C
| METERNUM | READDATETME1 | READAMOUNT1 | READDATETME2 | READAMOUNT2 |
|---|---|---|---|---|
| 0001 | 20240501 01:00 | 100 | 20240501 02:00 | 120 |
| 0001 | 20240502 01:00 | 110 | 20240502 02:00 | 115 |
| METERNUM | READDATETME | READAMOUNT |
|---|---|---|
| 0001 | 20240501 01:00 | 100 |
| 0001 | 20240501 02:00 | 120 |
| 0001 | 20240502 01:00 | 110 |
| 0001 | 20240502 02:00 | 115 |