How to handle null values in pivot SP(sqlserver)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to handle null values in pivot SP(sqlserver)

Author

Topic

naresh0407
Starting Member

30 Posts

Posted - 2010-11-02 : 12:49:21

Hi,

How to handle null value

Here i should handle null values

PIVOT
(
max(Head)

This is my table.If i dont have data for particular date i am getting null but it should return 0.

CREATE TABLE SAR_HeadCount(
HeadCount int,
CampaignID int,
atnDate datetime,
)
Insert into SAR_HeadCount(HeadCount,CampaignID,atnDate)
select '40','1','8/1/2010' union all
select '','1','8/8/2010' union all
select '43','1','8/15/2010' union all
select '43','1','8/22/2010'

Please run this stored procedure it will return null for one column but it should return 0

exec SAR_Sp_GetForecastedHC '8/8/2010','9/15/2010','10/22/2010','10/29/2010',1

ALTER PROCEDURE [dbo].[SAR_Sp_GetForecastedHC](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = 'SELECT '+ CHAR(39) + 'Agents Scheduled as per Schedule' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT HeadCount as Head,
StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
AS SourceTable
PIVOT
(
max(Head)
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable
union
SELECT '+ CHAR(39) + 'Forecasted HC as per Hiring Plan' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT HeadCount as Head,
StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
AS SourceTable
PIVOT
(
max(Head)
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable'
EXEC(@query)
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-02 : 13:25:12

You basically need to build an ISNULL function into your code that constructs your column selections

so that this:
[08/08/2010], [09/15/2010]

ends up being this:
isNull([08/08/2010],0) [08/08/2010], isNull([09/15/2010], 0) [09/15/2010]

use "PRINT @query" rather than "EXEC(@query)" to help debug the code.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources