unpivot one row of aggregates to 1 column

Question

I have this query that returns 1 row of aggregate sums DECLARE @Income9 int

SELECT @Income9 = IncomeLevel FROM PovertyLevels WHERE HouseholdNumber = 9
;WITH CTE
AS
(
SELECT PatientProfileID, CASE WHEN v.FamilyMembersinHousehold > 8 
                THEN ROUND((CAST(AnnualIncome as float)/(CAST(@Income9 as float) +((V.FamilyMembersinHousehold-8)* CAST(@Income9 as Float)))*100.00), 5)   
            WHEN ((v.FamilyMembersinHousehold IS NULL) OR (AnnualIncome IS NULL)) THEN NULL
            ELSE ROUND(((CAST(AnnualIncome AS Float)/CAST(pl.IncomeLevel as Float)) * 100.00), 5) END AS PercentOfPoverty
FROM vPatientDemographics v 
    LEFT OUTER JOIN PovertyLevels pl ON v.FamilyMembersinHousehold = pl.HouseholdNumber 
)
SELECT  SUM(CASE WHEN PercentOfPoverty <= 100 THEN 1 ELSE 0 END) AS NumOfPatientsBelow100,
       SUM(CASE WHEN PercentOfPoverty BETWEEN 101 AND 150 THEN 1 ELSE 0 END) AS NumOfPatientsBetween101And150,
       SUM(CASE WHEN PercentOfPoverty BETWEEN 151 AND 200 THEN 1 ELSE 0 END) AS NumOfPatientsBetween151And200,
       SUM(CASE WHEN PercentOfPoverty > 200 THEN 1 ELSE 0 END) AS NumOfPatientsOver200,
       SUM(CASE WHEN PercentOfPoverty IS NULL THEN 1 ELSE 0 END) AS NumOfPatientsUnknown
FROM CTE 

I would like to have the sum data to be in rows not columns. I tried adding this UNPIVOT but it does not recognize the column names.

UNPIVOT
(
    Levels for PovertyLevels in (NumOfPatientsBelow100, NumOfPatientsBetween101And150, NumOfPatientsBetween151And200,
        NumOfPatientsOver200, NumOfPatientsUnknown)
) as Unpvt

How can I unpivot the initial data set to that it is in rows not columns?

Answer 1

It is because Where clause is evaluated before the select

SELECT @Income9 = IncomeLevel FROM PovertyLevels WHERE HouseholdNumber = 9
;WITH CTE
AS
(
SELECT PatientProfileID, CASE WHEN v.FamilyMembersinHousehold > 8 
                THEN ROUND((CAST(AnnualIncome as float)/(CAST(@Income9 as float) +((V.FamilyMembersinHousehold-8)* CAST(@Income9 as Float)))*100.00), 5)   
            WHEN ((v.FamilyMembersinHousehold IS NULL) OR (AnnualIncome IS NULL)) THEN NULL
            ELSE ROUND(((CAST(AnnualIncome AS Float)/CAST(pl.IncomeLevel as Float)) * 100.00), 5) END AS PercentOfPoverty
FROM vPatientDemographics v 
    LEFT OUTER JOIN PovertyLevels pl ON v.FamilyMembersinHousehold = pl.HouseholdNumber 
),intr as
(
SELECT  SUM(CASE WHEN PercentOfPoverty <= 100 THEN 1 ELSE 0 END) AS NumOfPatientsBelow100,
       SUM(CASE WHEN PercentOfPoverty BETWEEN 101 AND 150 THEN 1 ELSE 0 END) AS NumOfPatientsBetween101And150,
       SUM(CASE WHEN PercentOfPoverty BETWEEN 151 AND 200 THEN 1 ELSE 0 END) AS NumOfPatientsBetween151And200,
       SUM(CASE WHEN PercentOfPoverty > 200 THEN 1 ELSE 0 END) AS NumOfPatientsOver200,
       SUM(CASE WHEN PercentOfPoverty IS NULL THEN 1 ELSE 0 END) AS NumOfPatientsUnknown
FROM CTE 
)
Select cnt,range from intr 
cross apply (values (NumOfPatientsBelow100,'NumOfPatientsBelow100'),
(NumOfPatientsBetween101And150,'NumOfPatientsBetween101And150'),
(NumOfPatientsBetween151And200,'NumOfPatientsBetween151And200'),
(NumOfPatientsOver200,'NumOfPatientsOver200'),
(NumOfPatientsUnknown,'NumOfPatientsUnknown')) cs (cnt,range)