how to write Pivot Query for Following

  • Hi Everyone

    I have wrote a query as

    SELECT PD.Name,PD.ContactNumber,Sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0)))[Allocation],

    'Week '+cast(DATEPART(wk, GI.EnteredOn)as varchar(50)) AS WeekNumber,

    sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0))) AS [Consumtion]

    FROM PDS_GodownCallInfo GI

    full join PDS_GodownAllocation GA on GA.ContactNumber=GI.ContactNumber

    full join PDS_ShopOwnnerContactDetails PD on PD.ContactNumber=GI.ContactNumber

    WHERE GI.EnteredOn >= '2013-01-01' AND GI.EnteredOn < '2013-11-30'

    GROUP BY DATEPART(wk, GI.EnteredOn),PD.Name,PD.ContactNumber

    and its output is as

    NameContactNumberAllocationWeekNumberConsumtion

    NULLNULL1485Week 441485

    Amit Singh8091366307220Week 44220

    Anil Semwal94184977221500Week 441500

    Rakesh Thakur8091022334220Week 44220

    Rakesh Verma8627811198220Week 44220

    NULLNULL1445Week 451445

    Amit Singh80913663071446Week 451446

    Rakesh Thakur80910223340Week 450

    Samridh Dhawan980539662245Week 4545

    then I write a pivot query as below:

    SELECT *

    FROM (

    SELECT PD.Name,PD.ContactNumber,Sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0)))[Allocation],

    'Week '+cast(DATEPART(wk, GI.EnteredOn)as varchar(50)) AS WeekNumber,

    sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0))) AS [Consumtion]

    FROM PDS_GodownCallInfo GI

    full join PDS_GodownAllocation GA on GA.ContactNumber=GI.ContactNumber

    full join PDS_ShopOwnnerContactDetails PD on PD.ContactNumber=GI.ContactNumber

    WHERE GI.EnteredOn >= '2013-01-01' AND GI.EnteredOn < '2013-11-30'

    GROUP BY DATEPART(wk, GI.EnteredOn),PD.Name,PD.ContactNumber

    ) as data

    PIVOT

    (

    sum(Consumtion)

    FOR [WeekNumber] IN (week44,week45)

    )AS p

    and Its output is as bellow

    NameContactNumberAllocationweek44week45

    NULLNULL1445NULLNULL

    NULLNULL1485NULLNULL

    Amit Singh8091366307220NULLNULL

    Amit Singh80913663071446NULLNULL

    Anil Semwal94184977221500NULLNULL

    Rakesh Thakur80910223340NULLNULL

    Rakesh Thakur8091022334220NULLNULL

    Rakesh Verma8627811198220NULLNULL

    Samridh Dhawan980539662245NULLNULL

    there is values for Consumtion is showing NULL.

    can any one help me how can I fix the issue?

  • First thing to do is be consistent with what you are calling your weeks. They start odd as "Week 44" and "Week 45" but you're pivoting on "Week44" and "Week45".

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

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