SQL query to unpivot and union for multiple column

I’m trying to achieve the following result…

input, table A

-----+--------+--------+-----+-------+----------+----------+---------+
sub  |   c_f  |  type  | F_G | layer | dec_2020 | jan_2021 | feb_2021
-----+--------+--------+-----+-------+----------+----------+---------+
A600 |  Core  | Analog |  F  |  50   |  d_val_1 | j_val_1  | f_val_1
A600 |  Core  | Analog |  F  |  60   |  d_val_2 | j_val_2  | f_val_2
A600 |  future| Analog |  G  |  32   |  d_val_3 | j_val_3  | f_val_3
-----+--------+--------+-----+-------+----------+----------+---------+

I would obtain this output

-----------+-----+--------+--------+-----+-------+----------+
month_year | sub |   c_f  |  type  | F_G | layer | values   
-----------+-----+--------+--------+-----+-------+----------+
dec_2020   |A600 |  Core  | Analog |  F  |  50   |  d_val_1 
dec_2020   |A600 |  Core  | Analog |  F  |  60   |  d_val_2 
dec_2020   |A600 |  future| Analog |  G  |  32   |  d_val_3 
jan_2021   |A600 |  Core  | Analog |  F  |  50   |  j_val_1  
jan_2021   |A600 |  Core  | Analog |  F  |  60   |  j_val_2    
jan_2021   |A600 |  future| Analog |  G  |  32   |  j_val_3   
feb_2021   |A600 |  Core  | Analog |  F  |  50   |  f_val_1  
feb_2021   |A600 |  Core  | Analog |  F  |  60   |  f_val_2
feb_2021   |A600 |  future| Analog |  G  |  32   |  f_val_3
-----+--------+--------+-----+-------+----------+----------+

I’m trying to use cross apply and I can create the column “month_year” but I don’t know how to merge “values” in the last column. Thanks for help!

Answer

I think this is basically apply:

select v.month_year, a.sub, a.c_f, a.type, a.F_G, a.layer, v.value
from a cross apply
     (values ('dec_2020', a.dec_2020),
             ('jan_2021', a.jan_2021),
             ('feb_2021', a.feb_2021)
     ) v(month_year, value);

Advertisement

Answer