SQL SERVER Replacing the null value in dynamic PIVOT

Question

Good day/night to all. I'm new in stored procedure, i have lack of experience and understanding when it comes to stored procedure. I tried the other tutorial and answers but i don't know why my query wasnt working when using isnull(payment,'0') or coalesce(payment,'0').

declare @sql as nvarchar(max) = '[2013-04-01],[2013-04-02],[2013-04-03],[2013-04-04],[2013-04-05],[2013-04-06]';
declare @name as nvarchar(max) = 'Derichel'
set @sql =
'SELECT pid, [Fullname], ' + @sql + '
FROM
(SELECT pid, [Fullname], payment, dateregistration
from tbl_Personal
) AS s
PIVOT
(
min(payment)
FOR dateregistration IN (' + @sql + ')
) AS pvt
where [Fullname] = ''' + @name + '''
order by pid'
execute sp_executesql @sql;

Some answer and tutorials have fixed column inside IN (). My @sql has been set to different date(it depends on user input from gui).

How can i replace the null value to 0?

the output of above code is

pid Fullname [2013-04-01] [2013-04-02] [2013-04-03] [2013-04-04] [2013-04-05] [2013-04-06]

6   Derichel     NULL         NULL          NULL         NULL        NULL           0

i want to replace the null to 0.

Answer 1

You are getting the NULL values because there are no rows for the dates. When you try to include the ISNULL function on the root query (SELECT ... FROM tbl_Personal), there is nothing to modify (the row doesn't exist).

The NULL values appear as a result of the PIVOT operation, so you need to apply the ISNULL after the data is pivoted. Another way to look at it is to apply the ISNULL to the definition of the final results, which is the first SELECT clause.

Here's the SQL statement without the formatting for a dynamic pivot query.

SELECT pid, [Fullname],
  ISNULL([2013-04-01], 0) AS [2013-04-01],
  ISNULL([2013-04-02], 0) AS [2013-04-02],
  ISNULL([2013-04-03], 0) AS [2013-04-03],
  ISNULL([2013-04-04], 0) AS [2013-04-04],
  ISNULL([2013-04-05], 0) AS [2013-04-05],
  ISNULL([2013-04-06], 0) AS [2013-04-06]
FROM
(SELECT pid, [Fullname], payment, dateregistration
from tbl_Personal
) AS s
PIVOT
(
min(payment)
FOR dateregistration IN ([2013-04-01],[2013-04-02],[2013-04-03],[2013-04-04],[2013-04-05],[2013-04-06])
) AS pvt
where [Fullname] = 'Derichel'
order by pid

For the dynamic query, you won't be able to use the @SQL variable in both places that you use it now. The first instance would contain the ISNULL function calls, which are not allowed in the second instance (FOR dateregistration IN...).