CROSS APPLY versus UNPIVOT

cross-apply, sql, sql-server, unpivot

Solution

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);

Problem

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) ```

Original source