Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to write Pivot Query for Following


how to write Pivot Query for Following

Author
Message
amitsingh308
amitsingh308
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 224
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

Name ContactNumber Allocation WeekNumber Consumtion
NULL NULL 1485 Week 44 1485
Amit Singh 8091366307 220 Week 44 220
Anil Semwal 9418497722 1500 Week 44 1500
Rakesh Thakur 8091022334 220 Week 44 220
Rakesh Verma 8627811198 220 Week 44 220
NULL NULL 1445 Week 45 1445
Amit Singh 8091366307 1446 Week 45 1446
Rakesh Thakur 8091022334 0 Week 45 0
Samridh Dhawan 9805396622 45 Week 45 45


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
Name ContactNumber Allocation week44 week45
NULL NULL 1445 NULL NULL
NULL NULL 1485 NULL NULL
Amit Singh 8091366307 220 NULL NULL
Amit Singh 8091366307 1446 NULL NULL
Anil Semwal 9418497722 1500 NULL NULL
Rakesh Thakur 8091022334 0 NULL NULL
Rakesh Thakur 8091022334 220 NULL NULL
Rakesh Verma 8627811198 220 NULL NULL
Samridh Dhawan 9805396622 45 NULL NULL


there is values for Consumtion is showing NULL.


can any one help me how can I fix the issue?
Richard Warr
Richard Warr
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2578 Visits: 1983
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".

_____________________________________________________________________
MCSA SQL Server 2012
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search