Menu
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?
306
I've found that replace values does not pick up new columns when added to the table, thus, the unpivot removes the new column's rows with null values.
The PIVOT statement is used to convert table rows into columns, while the UNPIVOT operator converts columns back to rows. Reversing a PIVOT statement refers to the process of applying the UNPIVOT operator to the already PIVOTED dataset in order to retrieve the original dataset.
Rotates a table by transforming columns into rows. UNPIVOT is a relational operator that accepts two columns (from a table or subquery), along with a list of columns, and generates a row for each column specified in the list.
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.
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
