SQL Server - Include NULL using UNPIVOT

Question

UNPIVOT will not return NULLs, but I need them in a comparison query. I am trying to avoid using ISNULL the following example (Because in the real sql there are over 100 fields):

Select ID, theValue, column_name
From 
(select ID,
  ISNULL(CAST([TheColumnToCompare]  AS VarChar(1000)), '') as TheColumnToCompare
  from MyView
  where The_Date = '04/30/2009'
) MA
UNPIVOT
   (theValue FOR column_name IN 
   ([TheColumnToCompare])
) AS unpvt

Any alternatives?

Answer 1

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

Answer 2

Writing in May'22 with testing it on AWS Redshift. You can use a with clause where you can coalesce the columns where nulls are expected. Alternatively, you can use coalesce in the select statement prior to the UNPIVOT block.

And don't forget to alias with the original column name (Not following won't break or violate the rule but would save some time for coffee).

Select ID, theValue, column_name
From 
(select ID,
  coalesce(CAST([TheColumnToCompare]  AS VarChar(1000)), '') as TheColumnToCompare
  from MyView
  where The_Date = '04/30/2009'
) MA
UNPIVOT
   (theValue FOR column_name IN 
   ([TheColumnToCompare])
) AS unpvt

OR

WITH TEMP1 as (
select ID,
  coalesce(CAST([TheColumnToCompare]  AS VarChar(1000)), '') as TheColumnToCompare
  from MyView
  where The_Date = '04/30/2009'
)

Select ID, theValue, column_name
From 
(select ID, TheColumnToCompare
  from MyView
  where The_Date = '04/30/2009'
) MA
UNPIVOT
   (theValue FOR column_name IN 
   ([TheColumnToCompare])
) AS unpvt

Answer 3

To preserve NULLs, use CROSS JOIN ... CASE:

select a.ID, b.column_name
, column_value = 
    case b.column_name
      when 'col1' then a.col1
      when 'col2' then a.col2
      when 'col3' then a.col3
      when 'col4' then a.col4
    end
from (
  select ID, col1, col2, col3, col4 
  from table1
  ) a
cross join (
  select 'col1' union all
  select 'col2' union all
  select 'col3' union all
  select 'col4'
  ) b (column_name)

Instead of:

select ID, column_name, column_value
From (
  select ID, col1, col2, col3, col4
  from table1
  ) a
unpivot (
  column_value FOR column_name IN (
    col1, col2, col3, col4)
  ) b

A text editor with column mode makes such queries easier to write. UltraEdit has it, so does Emacs. In Emacs it's called rectangular edit.

You might need to script it for 100 columns.

Answer 4

I ran into the same problem. Using CROSS APPLY (SQL Server 2005 and later) instead of Unpivot solved the problem. I found the solution based on this article An Alternative (Better?) Method to UNPIVOT and I made the following example to demonstrate that CROSS APPLY will NOT Ignore NULLs like Unpivot.

create table #Orders (OrderDate datetime, product nvarchar(100), ItemsCount float, GrossAmount float, employee nvarchar(100))

 insert into #Orders
 select getutcdate(),'Windows',10,10.32,'Me'
 union 
 select getutcdate(),'Office',31,21.23,'you'
 union 
 select getutcdate(),'Office',31,55.45,'me'
 union  
 select getutcdate(),'Windows',10,null,'You'

SELECT OrderDate, product,employee,Measure,MeasureType
 from #Orders orders
 CROSS APPLY (
    VALUES ('ItemsCount',ItemsCount),('GrossAmount',GrossAmount)
    ) 
    x(MeasureType, Measure) 


SELECT OrderDate, product,employee,Measure,MeasureType
from #Orders orders
UNPIVOT
   (Measure FOR MeasureType IN 
      (ItemsCount,GrossAmount)
)AS unpvt;


 drop table #Orders

Answer 5

ISNULL is half the answer. Use NULLIF to translate back to NULL. E.g.

DECLARE @temp TABLE(
    Foo varchar(50),
    Bar varchar(50) NULL
    );

INSERT INTO @temp( Foo,Bar )VALUES( 'licious',NULL );

SELECT * FROM @temp;

SELECT 
    Col,
    NULLIF( Val,'0Null' ) AS Val 
FROM(
    SELECT
        Foo,
        ISNULL( Bar,'0Null' ) AS Bar
    FROM
        @temp
    ) AS t
UNPIVOT(
    Val FOR Col IN(
        Foo,
        Bar 
        )
    ) up;

Here I use "0Null" as my intermediate value. You can use anything you like. However, you risk collision with user input if you choose something real-world like "Null". Garbage works fine "!@#34())0" but may be more confusing to future coders. I am sure you get the picture.

Answer 6

or, in SQLServer 2008 in shorter way:

...
cross join 
(values('col1'), ('col2'), ('col3'), ('col4')) column_names(column_name)

Answer 7

I've found left outer joining the UNPIVOT result to the full list of fields, conveniently pulled from INFORMATION_SCHEMA, to be a practical answer to this problem in some contexts.

-- test data
CREATE TABLE _t1(name varchar(20),object_id varchar(20),principal_id varchar(20),schema_id varchar(20),parent_object_id varchar(20),type varchar(20),type_desc varchar(20),create_date varchar(20),modify_date varchar(20),is_ms_shipped varchar(20),is_published varchar(20),is_schema_published varchar(20))
INSERT INTO _t1 SELECT 'blah1', 3, NULL, 4, 0, 'blah2', 'blah3', '20100402 16:59:23.267', NULL, 1, 0, 0 

-- example
select c.COLUMN_NAME, Value
from INFORMATION_SCHEMA.COLUMNS c
left join (
  select * from _t1
) q1
unpivot (Value for COLUMN_NAME in (name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published)
) t on t.COLUMN_NAME = c.COLUMN_NAME
where c.TABLE_NAME = '_t1'
</pre>

output looks like:

+----------------------+-----------------------+
|    COLUMN_NAME       |        Value          |
+----------------------+-----------------------+
| name                 | blah1                 |
| object_id            | 3                     |
| principal_id         | NULL                  | <======
| schema_id            | 4                     |
| parent_object_id     | 0                     |
| type                 | blah2                 |
| type_desc            | blah3                 |
| create_date          | 20100402 16:59:23.26  |
| modify_date          | NULL                  | <======
| is_ms_shipped        | 1                     |
| is_published         | 0                     |
| is_schema_published  | 0                     |
+----------------------+-----------------------+
    

Answer 8

Using dynamic SQL and COALESCE, I solved the problem like this:

DECLARE @SQL NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
DECLARE @dataCols NVARCHAR(MAX)

SELECT 
    @dataCols = COALESCE(@dataCols + ', ' + 'ISNULL(' + Name + ',0) ' + Name , 'ISNULL(' + Name + ',0) ' + Name )
FROM Metric WITH (NOLOCK)
ORDER BY ID

SELECT 
    @cols = COALESCE(@cols + ', ' + Name , Name )
FROM Metric WITH (NOLOCK)
ORDER BY ID

SET @SQL = 'SELECT ArchiveID, MetricDate, BoxID, GroupID, ID MetricID, MetricName, Value
            FROM 
               (SELECT ArchiveID, [Date] MetricDate, BoxID, GroupID,  ' + @dataCols + '
                FROM MetricData WITH (NOLOCK)
                INNER JOIN Archive WITH (NOLOCK)
                    ON ArchiveID = ID
                WHERE BoxID = ' + CONVERT(VARCHAR(40), @BoxID) + '
                AND GroupID = ' + CONVERT(VARCHAR(40), @GroupID) + ') p
            UNPIVOT
               (Value FOR MetricName IN 
                  (' + @cols + ')
            )AS unpvt
            INNER JOIN Metric WITH (NOLOCK)
                ON MetricName  = Name
            ORDER BY MetricID, MetricDate'

EXECUTE( @SQL )

Answer 9

It's a real pain. You have to switch them out before the UNPIVOT, because there is no row produced for ISNULL() to operate on - code generation is your friend here.

I have the problem on PIVOT as well. Missing rows turn into NULL, which you have to wrap in ISNULL() all the way across the row if missing values are the same as 0.0 for example.