Include NULL values in unpivot

Question

I have been looking for a solution for this problem for quite a long time. But, couldn't find any.

I have a table as below:

Month   Col_1   Col_2   Col_3   Col_4   Col_5   
---------------------------------------------
Jan     NULL    NULL    1       1       1   

I want to unpivot this table inorder to join with another table on fieldnames (Col_1,Col2,etc).

My query:

select Month,Name,value from 
TableName
    unpivot
    (
        Value 
        for Name in (Col_1,Col_2,Col_3,Col_4,Col_5)  
    ) u 

Current Result:

this gives me without the NULL values as below:

Month    Name    Value
-----------------------
Jan      Col_3   1
Jan      Col_4   1
Jan      Col_5   1

Expected Result:

I want the NULLs to be included in the result.

Month    Name    Value
-----------------------
Jan      Col_1   NULL
Jan      Col_2   NULL
Jan      Col_3   1
Jan      Col_4   1
Jan      Col_5   1

Any help would be appreciated.

Answer 1

SELECT name,value
FROM #Table1
CROSS APPLY (VALUES ('Col_1', Col_1),
                    ('Col_2', Col_2),
                    ('Col_3', Col_3),
                    ('Col_4', Col_4),
                    ('Col_5', Col_5)) 
            CrossApplied (name, value)

output

name    value
Col_1   NULL
Col_2   NULL
Col_3   1
Col_4   1
Col_5   1