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.

May it Help, http://stackoverflow.com/questions/1002989/sql-server-include-null-using-unpivot — ahmed abdelqader, Mar 07 '17 at 08:02

score 2 · Accepted Answer · answered Mar 07 '17 at 09:11

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

Vinoth Raj · Answer 2 · 2017-03-07T08:41:43.937

0

Query:

select c.column_name,value
from INFORMATION_SCHEMA.COLUMNS c
left join(select * from 'tablename') t1
unpivot(value for column_name in (col_1,col_2,col_3,col_4,col_5)) t2
on t2.column_name=c.COLUMN_NAME where c.TABLE_NAME='tablename'

edited Mar 07 '17 at 08:41

answered Mar 07 '17 at 08:23

Vinoth Raj

296
1
14

I had tried this before. Not working. I am getting the same result. – user2538559 Mar 07 '17 at 08:27
This should give me the expected result. So, I suppose the issue is with the extra column I have in the table. Edited the question to add the column `Month`. – user2538559 Mar 07 '17 at 08:56

score 0 · Answer 3 · answered Mar 07 '17 at 09:21

You can use the following query as a work-around in case Col_1, Col_2, ... are guarnteed not to take a specific value, say -1:

select [Month], Name, NULLIF(value, -1) AS value 
from (
   select [Month],
          coalesce(Col_1, -1) AS Col_1,
          coalesce(Col_2, -1) AS Col_2,
          coalesce(Col_3, -1) AS Col_3,
          coalesce(Col_4, -1) AS Col_4,
          coalesce(Col_5, -1) AS Col_5
   from TableName) AS t
unpivot
(
    Value 
    for Name in (Col_1,Col_2,Col_3,Col_4,Col_5)
) AS u

Demo here

Ven · Answer 4 · 2022-07-12T10:55:16.763

I had your same problem and this is my quick and dirty solution :

your query :

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

replace with :

select Month,Name,value from 
    ( select 
          isnull(Month,'no-data') as Month,
          isnull(Name,'no-data') as Name,
          isnull(value,'no-data') as value from TableName
    ) as T1
unpivot
(
    Value 
    for Name in (Col_1,Col_2,Col_3,Col_4,Col_5)  
) u

ok the null value is replaced with a string, but all rows will be returned !!

Include NULL values in unpivot

4 Answers4