"CROSS JOIN" instead of "UNPIVOT" to return NULL values  

Posted by ReelTym

create table table1 ( ID int identity(1,1), col1 int, col2 int, col3 int, col4 int )
insert into table1 ( col1, col2, col3, col4 )
    select 1, 2, 3, null union all
    select 5, 6, null, 8 union all
    select 9, null, 11, 12 union all
    select 13, 14, 15, 16

Use:

select
    ID,
    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)
    /* SQL08 Syntax
    cross join (values('col1'), ('col2'), ('col3'), ('col4')) column_names(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

This entry was posted on Wednesday, October 20, 2010 and is filed under , . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments