Dynamic reference zero to 0

Question

I have a stored procedure that takes data from one table, goes through my dynamic stored procedure and displays it on the page. The problem is that there are a significant number of null entries. When I process this data on the page, I need to add each amount of fuel to the TerminalID. The problem occurs when it deletes null entries. I do not want the procedure to read through each row and column to convert zero to 0, and hoped to do so in SP.

For testing, I 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:

 >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 tried placing ISNULL (, 0) around mfd.FuelQuantity and around @Sourcecolumn. mfd.FuelQuantiity does not seem to have changed, while the @Sourcecolumn error-ed out error requiring ISNull () to require 2 arguments.

Am I looking at it wrong?

Answer 1

I would strongly recommend abandoning obsolete implicit joins.

You need to include ISNULL() in every element in the @sourcecolumn list in the SELECT . The reason that it generated the error is because the entire list of columns was enclosed in one statement: ISNULL(col1,col2,col3...,0) you need ISNULL(col1,0),ISNULL(col2,0)...

I would suggest creating a separate sourcecolumn variable for use in your SELECT .

Sort of:

 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 table below.

Here is 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 different names for the 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 rotate 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 below