SQL Server - Include NULL using UNPIVOT

null, sql-server, t-sql, unpivot

Solution

Using dynamic SQL and COALESCE, I solved the problem like this:

DECLARE @SQL NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
DECLARE @dataCols NVARCHAR(MAX)

SELECT 
    @dataCols = COALESCE(@dataCols + ', ' + 'ISNULL(' + Name + ',0) ' + Name , 'ISNULL(' + Name + ',0) ' + Name )
FROM Metric WITH (NOLOCK)
ORDER BY ID

SELECT 
    @cols = COALESCE(@cols + ', ' + Name , Name )
FROM Metric WITH (NOLOCK)
ORDER BY ID

SET @SQL = 'SELECT ArchiveID, MetricDate, BoxID, GroupID, ID MetricID, MetricName, Value
            FROM 
               (SELECT ArchiveID, [Date] MetricDate, BoxID, GroupID,  ' + @dataCols + '
                FROM MetricData WITH (NOLOCK)
                INNER JOIN Archive WITH (NOLOCK)
                    ON ArchiveID = ID
                WHERE BoxID = ' + CONVERT(VARCHAR(40), @BoxID) + '
                AND GroupID = ' + CONVERT(VARCHAR(40), @GroupID) + ') p
            UNPIVOT
               (Value FOR MetricName IN 
                  (' + @cols + ')
            )AS unpvt
            INNER JOIN Metric WITH (NOLOCK)
                ON MetricName  = Name
            ORDER BY MetricID, MetricDate'

EXECUTE( @SQL )

Problem

`UNPIVOT` will not return `NULL`s, 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?

Original source