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

  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

  SSCertifiable

  Points: 6958

  More actions

  November 7, 2013 at 2:56 am

  #1664686

  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