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?

Hart CO · Accepted Answer · 2013-09-02T17:21:02.307

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]

It's now giving me the fueltypeid in all values versus the fuel quantities: — ProjectUI, Sep 02 '13 at 15:59
45 1 1 3 2 5 6 4 68 1 1 3 2 5 6 4 91 1 1 3 2 5 6 4 105 1 1 3 2 5 6 4 298 1 1 3 2 5 6 4 415 1 1 3 2 5 6 4 417 1 1 3 2 5 6 4 — ProjectUI, Sep 02 '13 at 15:59
I didn't notice your column names, just need to add brackets to the portion I added, see updated answer. — Hart CO, Sep 02 '13 at 16:33
It works like a charm, though now I'm missing the column names for the quantities. — ProjectUI, Sep 02 '13 at 16:44
I think I may have fixed it. If you could verify for me: SET @Sourcecolumn2 = STUFF((SELECT distinct ',ISNULL([' + CAST(FuelTypeID as varchar(4)) + '],0) as Column' + CAST(FuelTypeID as varchar(4)) FROM tt_Manifest_Fuel_Distribution — ProjectUI, Sep 02 '13 at 16:48
Yeah, keep missing little bits since I couldn't test it, yeah need an alias to go along with the `ISNULL()` function. — Hart CO, Sep 02 '13 at 17:18
It's working on my system, though my numbers aren't matching reports from years ago. Regardless, I think that problem lies in the data migration. This issue is fixed. Thank you very much! — ProjectUI, Sep 02 '13 at 17:41

score 1 · Answer 2 · answered Dec 08 '14 at 10:38

Consider the below table

enter image description here

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

enter image description here

Dynamic pivot null to 0

2 Answers2