Pivot Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot Query

Author

Topic

sanand7984
Starting Member

9 Posts

Posted - 2008-03-24 : 03:20:35

SELECT
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1] AS [FinalProduct],
[Membership] = SUM([R].[Membership])

FROM
[RptMMRSummary1] [R] INNER JOIN [RefCmsState1] [S]
ON [R].[CmsStateCode] = [S].[CmsStateCode]
INNER JOIN [RefPlanBenefitPackage1] [P]
ON [R].[PlanBenefitPackageID] = [P].[PlanBenefitPackageID]
WHERE
[R].[PaymentMonth] IN ('200712', '200711', '200612')
-- [P].[Attribute1] IN ('HMO', 'PPO', 'PFFS', 'SNP', 'EVCSNP')
GROUP BY
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1]
-------------------------------------------------------------------
How do we use the pivot query for the above script. Layout as below

200801 Month / Year Selection

-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total

200712 Always Previous Month for the above selection Month /Year

-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total

200612 Always Previous year End Month for the above select Month / Year

-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total

Output :
[RegionCode],
[CmsStateShortName],
CurMonth_HMO, CurMonth_PPO, CurMonth_PFFS, CurMonth_SNP, CurMonth_EVCSNP,
PrevMonth_HMO, PrevMonth_PPO, PrevMonth_PFFS, PrevMonth_SNP, PrevMonth_EVCSNP,
PrevYrEndMonth_HMO, PrevYrEndMonth_PPO, PrevYrEndMonth_PFFS, PrevYrEndMonth_SNP, PrevYrEndMonth_EVCSNP

sanand7984
Starting Member

9 Posts

Posted - 2008-03-24 : 04:41:31

Any ideas about this script

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-24 : 05:36:02

Please post some sample data & the desired output.

sanand7984
Starting Member

9 Posts

Posted - 2008-03-24 : 07:04:17

How to PIVOT Query for the below script instead of using Case when Statement

SELECT
[R].[PaymentMonth],
[S].[RegionCode] AS Region,
[S].[CmsStateShortName]AS State,
[P].[Attribute1] AS [FinalProduct],
[R].[Membership],

SUM(CASE WHEN [P].[Attribute1] = 'HMO' THEN [R].[Membership] END) AS HMO,
SUM(CASE WHEN [P].[Attribute1] = 'PPO' THEN [R].[Membership] END) AS PPO,
SUM(CASE WHEN [P].[Attribute1] = 'PFFS' THEN [R].[Membership] END) AS PFFS,
SUM(CASE WHEN [P].[Attribute1] = 'SNP' THEN [R].[Membership] END) AS SNP,
SUM(CASE WHEN [P].[Attribute1] = 'EVCSNP' THEN [R].[Membership] END) AS EVCSNP

FROM
[RptMMRSummary1] [R] INNER JOIN [RefCmsState1] [S]
ON [R].[CmsStateCode] = [S].[CmsStateCode]
INNER JOIN [RefPlanBenefitPackage1] [P]
ON [R].[PlanBenefitPackageID] = [P].[PlanBenefitPackageID]

WHERE
[R].[PaymentMonth] IN ('200801','200712','200612')
-- [P].[Attribute1] IN ('HMO','PPO','PFFS','SNP','EVCSNP')

GROUP BY
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1] ,
[R].[Membership]

quote:
Originally posted by sanand7984

SELECT
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1] AS [FinalProduct],
[Membership] = SUM([R].[Membership])

FROM
[RptMMRSummary1] [R] INNER JOIN [RefCmsState1] [S]
ON [R].[CmsStateCode] = [S].[CmsStateCode]
INNER JOIN [RefPlanBenefitPackage1] [P]
ON [R].[PlanBenefitPackageID] = [P].[PlanBenefitPackageID]
WHERE
[R].[PaymentMonth] IN ('200712', '200711', '200612')
-- [P].[Attribute1] IN ('HMO', 'PPO', 'PFFS', 'SNP', 'EVCSNP')
GROUP BY
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1]
-------------------------------------------------------------------
How do we use the pivot query for the above script. Layout as below

200801 Month / Year Selection

-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total

200712 Always Previous Month for the above selection Month /Year

-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total

200612 Always Previous year End Month for the above select Month / Year

-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total

Output :
[RegionCode],
[CmsStateShortName],
CurMonth_HMO, CurMonth_PPO, CurMonth_PFFS, CurMonth_SNP, CurMonth_EVCSNP,
PrevMonth_HMO, PrevMonth_PPO, PrevMonth_PFFS, PrevMonth_SNP, PrevMonth_EVCSNP,
PrevYrEndMonth_HMO, PrevYrEndMonth_PPO, PrevYrEndMonth_PFFS, PrevYrEndMonth_SNP, PrevYrEndMonth_EVCSNP

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-24 : 07:12:15

Here is some simple example on how to use Pivot Query.

declare @tbl as table(Yr int,Quarter varchar(10),Amt int)
insert into @tbl
select 2000,'Q1',70 union all
select 2000,'Q2',20 union all
select 2000,'Q3',70 union all
select 2001,'Q2',70 union all
select 2002,'Q1',70

select * from @tbl pivot
(sum(amt) for quarter in(Q1,Q2,Q3))as p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources