Post reply

How to handle null values in pivot stored procedure

  • October 29, 2010 at 9:51 pm

    #225591

    Hi,

    How to handle null values below stored procedure returns null value when i dont have data.

    Below is the pivot query

    max(isnull(Head),0) is not working

    PIVOT

    (

    max(Head)

    FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')

    ) AS PivotTable'

    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) + '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

  • October 30, 2010 at 6:29 am

    #1244522

    please provide table def and sample data as described in the first link in my signature together with your expected result based on your sample.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply