SQL Server Performance

Pivot Query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Ashwinv, Mar 24, 2008.

  1. Ashwinv New Member

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

    FROM
    [RptMMRSummary1] [R] INNER JOIN [RefCmsState1]
    ON [R].[CmsStateCode] = .[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],
    .[RegionCode],
    .[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
  2. Madhivanan Moderator

    Did you read about Cross-tab reports or PIVOT in sql server help file

Share This Page