sqlsql-serverunpivot

Convert multiple rows to column (unpivot)


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

Solution

  • 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.