CROSS APPLY versus UNPIVOT

Question

I found that UNPIVOT is able to automatically exclude fields that have NULL values. However, CROSS APPLY-VALUES method is not able to do that. Does anyone know how to automatically exclude NULL value fields from CROSS APPLY-VALUES? Let's say, exclude field Field3 and Field4 if they contain NULL values.

SELECT 
    E.FieldA, 
    E.FieldB, 
    E.FieldC, 
    DBParam.Display, 
    DBParam.Value
INTO DBParam
FROM
Map_Data AS E
CROSS APPLY (VALUES (Field1, 'Field1'),
            (Field2, 'Field2'), 
            (Field3, 'Field3'),
            (Field4, 'Field4')
) AS DBParam(Value, Display)

Please note that tags stand alone. That is, you can't combine multiple tags to create a single concept. The tags `[sql]` and `[server]` together aren't the same thing as the single `[sql-server]` tag. **Always be sure to read the descriptions that appear when selecting tags!** — Charles, May 12 '14 at 03:17
Sorry, I don't have enough reputation to put in cross-apply. — Casperonian, May 12 '14 at 03:20
I'm not sure what makes you think you needed reputation to use that tag. You only require a certain level of rep to *create* tags, and [tag:cross-apply] exists. — Charles, May 12 '14 at 03:22

score 8 · Accepted Answer · answered May 12 '14 at 05:06

Instead of using VALUES, use SELECT:

SELECT E.FieldA, E.FieldB, E.FieldC, 
       DBParam.Display, DBParam.Value
INTO DBParam
FROM Map_Data E CROSS APPLY
     (SELECT Value, Display
      FROM (SELECT Field1 as Value, 'Field1' as Display UNION ALL
            SELECT Field2, 'Field2' UNION ALL
            SELECT Field3, 'Field3' UNION ALL
            SELECT Field4, 'Field4'
           ) vd
      WHERE Value is not null
     ) as DBParam(Value, Display);

CROSS APPLY versus UNPIVOT

1 Answers1