CODEWITHSUNDEEP
sqlsql-serverunpivot
user2538559
user2538559

Reputation: 59

Include NULL values in unpivot

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.

Upvotes: 3

Views: 5461

Answers (4)

Ven
Ven

Reputation: 86

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 !!

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

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

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

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

Upvotes: 3

Vinoth Raj
Vinoth Raj

Reputation: 296

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'

Check Snapshot

Upvotes: 0

Related Questions