Dynamic pivot null to 0

Question

I've got a stored procedure that is taking data from one table, running through my dynamic pivot stored procedure, and outputting into the page. The problem is, there is a substantial number of null entries. When I process this data on the page, I'm needing to add each of the fuel quantities per TerminalID. The issue arises when it hits the null entries. I don't want to have the procedure read through every row and column to convert null to 0, and was hoping to do so in the SP.

For Testing, I've made this script:

DECLARE @QUERY NVARCHAR(MAX)
    ,   @Soucecolumn VARCHAR(MAX)
    ,   @BeginningDate VARCHAR(MAX)
    ,   @EndingDate VARCHAR(MAX)
    ,   @CompanyID VARCHAR(2)

SET NOCOUNT ON;

SET @BeginningDate = CONVERT(VARCHAR(30), CAST('2004-01-01' AS DATE));
SET @EndingDate = CONVERT(VARCHAR(30), CAST('2007-01-01' AS DATE));
SET @CompanyID = CONVERT(INT, '2');
SET @Soucecolumn = STUFF((
    SELECT DISTINCT ', \[' + CAST(FuelTypeID AS VARCHAR(4)) + '\]'
    FROM tt_Manifest_Fuel_Distribution
    FOR XML PATH ('')), 1, 1, '')

SET @QUERY = '(
SELECT ManifestID, TerminalID, ' + @Soucecolumn + ' 
FROM (
    SELECT mfd.ManifestID, m.TerminalID, mfd.FuelTypeID, mfd.FuelQuantity 
    FROM tt_Manifest_Fuel_Distribution mfd, tt_Terminals t, tt_Fuel_Types ft, tt_Manifests m
    WHERE mfd.FuelTypeID=ft.FuelTypeID
        AND m.ManifestID=mfd.ManifestID
        AND m.CompanyID= ' + @CompanyID + ' 
        AND m.ManifestInsertDate BETWEEN ''' + @BeginningDate + ''' AND ''' + @EndingDate +
        ''' 
) up 
PIVOT (
    MAX(FuelQuantity) FOR \[FuelTypeID\] IN (' + @Soucecolumn + ')
) AS pvt)'

EXEC sp_executesql @QUERY

Sample data is:

>ManifestID   TerminalID   3   6   4   2   1   5
>417    1   NULL    NULL    NULL    NULL    NULL    2478
>421    1   NULL    NULL    NULL    NULL    3458    NULL
>508    1   NULL    NULL    NULL    NULL    NULL    2471
>826    1   NULL    NULL    NULL    NULL    NULL    7464
>832    1   NULL    NULL    NULL    NULL    3482    NULL
>833    1   1001    NULL    NULL    NULL    1492    NULL
>844    1   NULL    NULL    NULL    NULL    2498    NULL
>870    1   NULL    NULL    NULL    NULL    5991    2503
>872    1   NULL    NULL    NULL    NULL    3494    NULL
>2      2   NULL    NULL    5514    NULL    NULL    2505
>43     2   NULL    NULL    NULL    NULL    7011    NULL
>46     2   1005    NULL    NULL    NULL    5007    2510
>60     2   NULL    NULL    3502    NULL    NULL    4513
>63     2   NULL    NULL    4505    NULL    NULL    3008
>69     2   NULL    NULL    4008    NULL    4508    NULL
>78     2   1007    NULL    NULL    NULL    5022    NULL
>79     2   NULL    NULL    2505    NULL    NULL    NULL

I've tried placing ISNULL(,0) around the mfd.FuelQuantity, and around the @Sourcecolumn. mfd.FuelQuantiity seemed to have no change, while the @Sourcecolumn error-ed out claiming that the ISNull() required 2 arguments.

Am I looking at this in the wrong way?

Answer 1

I'd strongly suggest moving away from deprecated implicit joins.

You need to incorporate ISNULL() into each item in the @sourcecolumn list in the SELECT clause. The reason it threw an error is because your entire list of columns was wrapped in one statement: ISNULL(col1,col2,col3...,0) you need ISNULL(col1,0),ISNULL(col2,0)...

I'd suggest making a separate sourcecolumn variable for use in your SELECT.

Something like:

SET @Sourcecolumn2 = STUFF((SELECT distinct ',ISNULL(\[' + CAST(FuelTypeID as varchar(4)) + ',0)\]as '+ CAST(FuelTypeID as varchar(4)) +'     FROM tt_Manifest_Fuel_Distribution
FOR XML PATH('')),1,1,'') 

So ultimately:

![Declare    @QUERY NVARCHAR(MAX), 
@Soucecolumn VARCHAR(MAX),
@Sourcecolumn2 VARCHAR(MAX),
@BeginningDate VARCHAR(MAX),
@EndingDate VARCHAR(MAX),
@CompanyID VARCHAR(2)
SET NOCOUNT ON;
SET @BeginningDate = convert(varchar(30), cast('2004-01-01' as date));
SET @EndingDate = convert(varchar(30), cast('2007-01-01' as date));
SET @CompanyID = convert(int, '2');
SET @Soucecolumn = STUFF((SELECT distinct ', \[' + CAST(FuelTypeID as varchar(4)) + '\]'     FROM tt_Manifest_Fuel_Distribution
FOR XML PATH('')),1,1,''); 
SET @Sourcecolumn2 = STUFF((SELECT distinct ',ISNULL(\[' + CAST(FuelTypeID as varchar(4)) + ',0)\] as '+ CAST(FuelTypeID as varchar(4))+'     FROM tt_Manifest_Fuel_Distribution
FOR XML PATH('')),1,1,''); 
SET @QUERY = '(SELECT ManifestID, TerminalID, ' + @Sourcecolumn2 + ' FROM (
SELECT mfd.ManifestID, m.TerminalID, mfd.FuelTypeID, mfd.FuelQuantity 
FROM tt_Manifest_Fuel_Distribution mfd, tt_Terminals t, tt_Fuel_Types ft, tt_Manifests m
WHERE mfd.FuelTypeID=ft.FuelTypeID
AND m.ManifestID=mfd.ManifestID
AND m.CompanyID= ' + @CompanyID + ' 
AND m.ManifestInsertDate BETWEEN ''' + @BeginningDate + ''' AND ''' + @EndingDate + 
''' ) up PIVOT (MAX(FuelQuantity) FOR \[FuelTypeID\] IN (' + @Soucecolumn + ')) AS pvt)'
exec sp_executesql  @QUERY][1]

Answer 2

Consider the below table

Here is the sample data

SELECT * INTO #TEMP
FROM
(
    SELECT '01/JAN/2014' [DATE],'A' NAME,100 MARKS
    UNION ALL
    SELECT '02/JAN/2014' [DATE],'A' NAME,120
    UNION ALL
    SELECT '02/JAN/2014' [DATE],'B' NAME,130
    UNION ALL
    SELECT '03/JAN/2014' [DATE],'B' NAME,115
    UNION ALL
    SELECT '01/JAN/2014' [DATE],'C' NAME,123
    UNION ALL
    SELECT '01/JAN/2014' [DATE],'C' NAME,134
    UNION ALL
    SELECT '03/JAN/2014' [DATE],'C' NAME,146
    UNION ALL
    SELECT '04/JAN/2014' [DATE],'C' NAME,149
)TAB

Now select the distinct names to a variable for pivot

DECLARE @cols NVARCHAR (MAX)
SET @cols = SUBSTRING((SELECT DISTINCT ',['+NAME+']' 
FROM #TEMP GROUP BY NAME FOR XML PATH('')),2,8000)

Now you need another variable to apply the NULL to zero logic

DECLARE @NulltoZeroCols NVARCHAR (MAX)
SET @NulltoZeroCols = SUBSTRING((SELECT DISTINCT ',ISNULL(['+NAME+'],0) AS ['+NAME+']' 
FROM #TEMP GROUP BY NAME FOR XML PATH('')),2,8000)

Now pivot the query using both variables

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT DATE,' + @NulltoZeroCols + ' FROM 
             (
                 SELECT [DATE],NAME,MARKS FROM #TEMP
             ) x
             PIVOT 
             (
                 SUM(MARKS)
                 FOR [NAME] IN (' + @cols + ')
            ) p
            ;'
EXEC SP_EXECUTESQL @query

Finally your result is as below