Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to write Pivot Query for Following Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 2:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:55 PM
Points: 64, Visits: 205
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?
Post #1512160
Posted Thursday, November 7, 2013 2:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:46 AM
Points: 2,153, Visits: 1,744
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".
Post #1512167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse