SQL Server - Include NULL using UNPIVOT
Solution 1
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.
Solution 2
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.
Solution 3
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
Solution 4
or, in SQLServer 2008 in shorter way:
...
cross join
(values('col1'), ('col2'), ('col3'), ('col4')) column_names(column_name)
Solution 5
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 | +----------------------+-----------------------+
JeffO
Updated on March 26, 2020Comments
-
JeffO over 4 years
UNPIVOT
will not returnNULL
s, but I need them in a comparisonquery
. I am trying to avoid usingISNULL
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?