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.
See Question&Answers more detail:os