How to get this result using Pivot or Unpivot

Question

i have a query in which i am getting result like this and i am geting the result set in below. But i need to get as shown in last one

SELECT DISTINCT                     LEFT(DATENAME(m, CONVERT(DATE, CONVERT(VARCHAR(10), fcp.DateKey))), 3) + ' ' + CONVERT(VARCHAR(4), year(convert(DATE, CONVERT(VARCHAR(10), fcp.DateKey)))) DatePeriod
        ,OM.HedisOCMKey AS OutComeMeasureKey
        ,(
            SELECT NumeratorSegmentCnt
            FROM Fact.HedisOCMSegments omse
            WHERE omse.SegmentLabel = 'Good'
                AND omse.HedisOCMKey = OM.HedisOCMKey
            ) AS Good
        ,0 AS Fair
        ,0 AS Poor
        ,0 AS 'NotCategorised'
                FROM fact.HEDISReport fcp WITH (NOLOCK)
    INNER JOIN fact.HedisOCM OM WITH (NOLOCK)
        ON OM.HEDISReportKey = fcp.HEDISReportKey
    INNER JOIN dim.ConditionMetric dc WITH (NOLOCK)
        ON dc.ConditionMetricID = OM.MetricID
    WHERE fcp.DateKey <= @i_ReportingPeriod

My out put like this one :

ConditionPrevalenceKey  PopulationConditionCnt  PrevalencePercent   DatePeriod  OutComeMeasureKey   Good    Fair    Poor    NotCategorised  NotTested   GoodPercentage  FairPercentage  PoorPercentage  NotCategorisedPercentage    NotTestedPercentage GoodRange   FairRange   PoorRange   NotCategorisedRange NotTestedRange  DerivedGoodValue    DerivedFairValue    DerivedPoorValue    DerivedNotCategorised   DerivedNotTested    ConditionMetricName


18018   252 0.53    Dec-12  34957   35  0   0   0   217 13.89   0   0   0   86.11   Good    Fair    Poor    NC  NoData  >= 1    0   0   NotCategorized  NotTested   Chlamydia  Screen

then how can i get my result set like this one :

year    Legend  percent count   Derived value field


Dec-12  Good    13.89   35  >= 1

Dec-12  Fair    0   0   0

Dec-12  Poor    0   0   0

Dec-12  NC  0   0   0

Suggest me ???

score 0 · Accepted Answer · edited May 23 '17 at 11:49

I would use UNPIVOT. There are many threads on UNPIVOT here on stackoverflow. Here is one example. Here is another that is very similar.

Here is a general start to solving your specific problem using UNPIVOT:

SELECT  [DatePeriod] ,
        [OutComeMeasureKey] ,
        [Legend] ,
        [Percent]
FROM    (SELECT DISTINCT
                LEFT(DATENAME(m, CONVERT(DATE, CONVERT(VARCHAR(10), fcp.DateKey))), 3) + ' ' + CONVERT(VARCHAR(4), YEAR(CONVERT(DATE, CONVERT(VARCHAR(10), fcp.DateKey)))) DatePeriod ,
                OM.HedisOCMKey AS OutComeMeasureKey ,
                (SELECT NumeratorSegmentCnt
                 FROM   Fact.HedisOCMSegments omse
                 WHERE  omse.SegmentLabel = 'Good' AND omse.HedisOCMKey = OM.HedisOCMKey
                ) AS Good ,
                0 AS Fair ,
                0 AS Poor ,
                0 AS 'NotCategorised'
         FROM   fact.HEDISReport fcp WITH (NOLOCK)
                INNER JOIN fact.HedisOCM OM WITH (NOLOCK) ON OM.HEDISReportKey = fcp.HEDISReportKey
                INNER JOIN dim.ConditionMetric dc WITH (NOLOCK) ON dc.ConditionMetricID = OM.MetricID
         WHERE  fcp.DateKey <= @i_ReportingPeriod
        ) AS sourcetable UNPIVOT ( [Percent] FOR Legend IN (Good, Fair, Poor, [NotCategorised]) ) AS unpvt

How to get this result using Pivot or Unpivot

1 Answers1