How to replace null values to 0 in dynamic pivot

Question

5.00/5 (1 vote)

See more:

Hi All,
I tried to convert the (null) values with 0 (zeros) output in dynamic PIVOT function but have no sucess.
i tried with below code but getting below error,

'SNULL' is not a recognized built-in function name.

SQL

DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT   @Scolumns = ISNULL(@Scolumns + ',','')+ 'ISNULL(' + QUOTENAME(Name) + ', 0) AS '+ QUOTENAME(Name)--HERE AM GETTING ABOVE ERROR
  FROM (
		SELECT Name  FROM #Temp

   ) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT SaleQuantity,CONVERT(VARCHAR,YEAR(MonthOf))+''_''+DATENAME(MM,MonthOF) AS MonthNames  FROM TEST (NOLOCK)
  
) AS j
PIVOT
(
  SUM(SaleQuantity) FOR MonthNames IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

DROP TABLE #Temp

thanks in advance

What I have tried:

How to Replace Null Values To 0 in Dynamic Pivot

Posted 4-May-16 1:00am

stellus

Updated 4-May-16 1:29am

CHill60

v2

Add a Solution

Comments

CHill60 4-May-16 7:04am

'SNULL' or 'ISNULL'?

stellus 4-May-16 7:09am

error am getting like that only,
'SNULL' is not a recognized built-in function name.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-05-04T01:29:00

If you print out your sql and examine it then you will find all sorts of errors - and not where you indicated!

You have already generated the comma separated list of columns to retrieve so there is no need to use

JavaScript

SELECT ' + STUFF(@columns, 1, 2, '')

That is the bit that is removing the "I" from the "ISNULL" that is in @Scolumns
You're doing something similar with

+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')

I'm assuming that #Temp contains a list of month names but it's still not clear what you are trying to do.

Something like this is closer to what you need

DECLARE @Scolumns NVARCHAR(MAX) -- this will include the ISNULL
DECLARE @columns NVARCHAR(MAX) -- this will just be the list of columns

SELECT   @scolumns = ISNULL(@Scolumns + ',','')+ 'ISNULL(' + QUOTENAME(Name) + ', 0) AS '+ QUOTENAME(Name)
FROM (
	SELECT Name  FROM #Temp
) AS x;

SELECT   @columns = ISNULL(@columns + ',','')+ QUOTENAME(Name) 
FROM (
	SELECT Name  FROM #Temp
) AS x;

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT ' + @Scolumns + '
FROM
(
  SELECT SaleQuantity,CONVERT(VARCHAR,YEAR(MonthOf))+''_''+DATENAME(MM,MonthOF) AS MonthNames  FROM TEST (NOLOCK)
  
) AS j
PIVOT
(
  SUM(SaleQuantity) FOR MonthNames IN (' + @columns  + ')
) AS p;';
PRINT @sql;

Note that you can't include the ISNULL in the list of columns for the PIVOT so you have to have two variables.

I suggest that you write your query for only a few rows (hard-code the column names) and make sure your query works. Then work on generating the list of columns dynamically - print these out and paste them into your manual, hard-coded query. Confirm it works
Then put it all together into the dynamic sql you need