How to Unpivot rows into columns while cross applying the first column?

I have the following sample data:

CASA    31-jan-2023     28-Feb-2023
-----------------------------------
CA      56.48           57.17
SA      18.74           17.26
TD      12.75           12.54

The output I want is:

CASA    Date            Deposit
-------------------------------
CA      31-jan-2023     56.48
CA      28-Feb-2023     57.17
SA      31-jan-2023     18.74 
SA      28-Feb-2023     17.26
TD      31-jan-2023     12.75
TD      28-Feb-2023     12.54

I have tried UNPIVOT and UNION ALL but that doesn’t get me the desired results. Help!

>Solution :

To unpivot a fixed list of columns, I would recommend values and cross apply:

select t.casa, x.*
from mytable t
cross apply ( values 
    ('2023-01-31', t.[31-jan-2023]),
    ('2023-01-31', t.[28-Feb-2023])
) x(date, deposit)

Leave a Reply