how to write Pivot Query for Following

Question

how to write Pivot Query for Following

amitsingh308

Ten Centuries

Points: 1287
More actions
November 7, 2013 at 2:35 am

#284572

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?

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

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

Richard Warr SSCertifiable Points: 6966 More actions · Answer 1

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".