Pivot table with rollup and case statement in the Grouping function

Question

I am trying to make a pivot table with rollup and a case statement in the grouping function.When the data in the column section of the pivot does not need to be altered my query works great,

his is an example.

http://sqlfiddle.com/#!3/3143e/2

But when I need to have a case statement in the column section it messes it up. For example One of the columns needs to return

“1 of 3” if someone voted in one of three elections, 
“2 of 3” if someone voted in two of three elections, 
“3 of 3” if someone voted in three of three elections,

This is the case statement

CAST( (CASE WHEN election1 IS NOT NULL THEN 1 ELSE 0 END)
       +(CASE WHEN election2 IS NOT NULL THEN 1 ELSE 0 END)
       +(CASE WHEN election3 IS NOT NULL THEN 1 ELSE 0 END) AS CHAR(1)) + '-3' as election

I need help figuring out how to put this case statement in the Grouping() function or maybe there is a different way to do this. I tried something like this

http://sqlfiddle.com/#!3/3143e/3

but of course it didn’t work. This is what the final pivot should look like

1 http://img842.imageshack.us/img842/6884/84834931.jpg

Thanks in advance

score 0 · Accepted Answer · answered Apr 11 '12 at 08:48

So i have a suggestion for you. I did it in another way but it works.

Test data

create table test4(city nvarchar(10), race nvarchar(30), sex nvarchar(10), age int, type nvarchar(30),election1 nvarchar(30), election2 nvarchar(30), election3 nvarchar(30),)
insert into test4 values ('Austin',  'African-American', 'male', 21, 'gv','G10','G08','G06')
insert into test4 values ('Austin',  'Asian', 'female', 22,'AV',null,'G08','G06')
insert into test4 values ('Austin',  'Caucasian', 'male', 23,'BV','G10',null,null)
insert into test4 values ('Austin',  'Hispanic', 'female', 24,'AV','G10','G08','G06')
insert into test4 values ('Austin',  'African-American', 'Unknown', 25,'CV','G10','G08',null)
insert into test4 values ('Austin',  'Asian', 'male', 26,'gv',null,'G08','G06')
insert into test4 values ('Austin',  'Caucasian', 'female', 27,'CV',null,'G08','G06')
insert into test4 values ('Austin',  'Hispanic', 'Unknown', 28,'AV',null,'G08','G06')
insert into test4 values ('Austin',  'Asian', 'male', 29,'BV','G10',null,'G06')
insert into test4 values ('Austin',  'Caucasian', 'female', 31,'gv','G10',null,'G06')
insert into test4 values ('Dallas',  'Hispanic', 'Unknown', 32,'BV','G10',null,'G06')
insert into test4 values ('Dallas',  'African-American', 'male', 33,'gv','G10',null,'G06')
insert into test4 values ('Dallas',  'Asian', 'female', 34,'BV',null,'G08','G06')
insert into test4 values ('Dallas',  'Caucasian', 'Unknown', 35,'AV',null,null,null)
insert into test4 values ('Dallas',  'Hispanic', 'male', 500,'AV',null,'G08',null)
insert into test4 values ('Dallas',  'African-American', 'female', 36,'AV','G10',null,'G06')
insert into test4 values ('Dallas',  'Asian', 'Unknown', 37,'CV','G10','G08',null)
insert into test4 values ('Dallas',  'Caucasian', 'male', 38,'CV',null,null,null)
insert into test4 values ('Dallas',  'Hispanic', 'female', 39,'gv','G10','G08','G06')
insert into test4 values ('Dallas',  'African-American', 'Unknown', 41,'CV',null,'G08','G06')
insert into test4 values ('Houston',  'Asian', 'male', 42,'BV',null,'G08',null)
insert into test4 values ('Houston',  'Caucasian', 'female', 43,'CV','G10',null,'G06')
insert into test4 values ('Houston',  'Hispanic', 'Unknown', 44,'BV','G10',null,'G06')
insert into test4 values ('Houston',  'African-American', 'male', 45,'CV',null,'G08','G06')
insert into test4 values ('Houston',  'Asian', 'female', 46,'CV','G10','G08','G06')
insert into test4 values ('Houston',  'Caucasian', 'Unknown', 47,'gv',null,null,null)
insert into test4 values ('Houston',  'Hispanic', 'male', 48,'AV','G10','G08',null)
insert into test4 values ('Houston',  'African-American', 'female', 49,'gv','G10',null,'G06')
insert into test4 values ('Houston',  'Asian', 'Unknown', 51,'BV',null,'G08',null)
insert into test4 values ('Houston',  'Caucasian', 'male', 52,'AV',null,'G08','G06');

A PIVOT with a UNION ALL

;WITH PivotTable
AS
(
    SELECT
        pvt.city,
        pvt.sex,
        pvt.election,
        ISNULL(pvt.[20_30_Af],0) AS [20_30_Af],
        ISNULL(pvt.[20_30_As],0) AS [20_30_As],
        ISNULL(pvt.[20_30_C],0) AS [20_30_C],
        ISNULL(pvt.[20_30_H],0) AS [20_30_H],
        ISNULL(pvt.[30_40_Af],0) AS [30_40_Af],
        ISNULL(pvt.[30_40_As],0) AS [30_40_As],
        ISNULL(pvt.[30_40_C],0) AS [30_40_C],
        ISNULL(pvt.[30_40_H],0) AS [30_40_H],
        ISNULL(pvt.[40_50_Af],0) AS [40_50_Af],
        ISNULL(pvt.[40_50_As],0) AS [40_50_As],
        ISNULL(pvt.[40_50_C],0) AS [40_50_C],
        ISNULL(pvt.[40_50_H],0) AS [40_50_H]
    FROM
    (
    SELECT
        (
        case 
            when race = 'African-American' and age between 21 and 30 
            then '20_30_Af' 
            when race = 'Asian' and age between 21 and 30
            then '20_30_As'
            when race = 'Caucasian' and age between 21 and 30
            then '20_30_C'
            when race = 'Hispanic' and age between 21 and 30
            then '20_30_H'
            when race = 'African-American' and age between 31 and 40
            then '30_40_Af'
            when race = 'Asian' and age between 31 and 40
            then '30_40_As'
            when race = 'Caucasian' and age between 31 and 40
            then '30_40_C'
            when race = 'Hispanic' and age between 31 and 40
            then '30_40_H'
            when race = 'African-American' and age between 41 and 50
            then '40_50_Af'
            when race = 'Asian' and age between 41 and 50
            then '40_50_As'
            when race = 'Caucasian' and age between 41 and 50
            then '40_50_C'
            when race = 'Hispanic' and age between 41 and 50
            then '40_50_H'
        end
        ) AS pivotText,
        1 as pivotNbr,
        test4.city,
        test4.sex,
        CAST( (CASE WHEN election1 IS NOT NULL THEN 1 ELSE 0 END)
           +(CASE WHEN election2 IS NOT NULL THEN 1 ELSE 0 END)
           +(CASE WHEN election3 IS NOT NULL THEN 1 ELSE 0 END) AS CHAR(1)) 
            + '-3' as election
    FROM
        test4
    ) AS p
    PIVOT
    (
        SUM(pivotNbr)
        FOR pivotText IN([20_30_Af],[20_30_As],
            [20_30_C],[20_30_H],[30_40_Af],
            [30_40_As],[30_40_C],[30_40_H],
            [40_50_Af],[40_50_As],[40_50_C],[40_50_H])
    ) AS pvt
)
SELECT
    PivotTable.city,
    PivotTable.sex,
    PivotTable.election,
    PivotTable.[20_30_Af],
    PivotTable.[20_30_As],
    PivotTable.[20_30_C],
    PivotTable.[20_30_H],
    PivotTable.[30_40_Af],
    PivotTable.[30_40_As],
    PivotTable.[30_40_C],
    PivotTable.[30_40_H],
    PivotTable.[40_50_Af],
    PivotTable.[40_50_As],
    PivotTable.[40_50_C],
    PivotTable.[40_50_H],
    (
        PivotTable.[20_30_Af]+
        PivotTable.[20_30_As]+
        PivotTable.[20_30_C]+
        PivotTable.[20_30_H]+
        PivotTable.[30_40_Af]+
        PivotTable.[30_40_As]+
        PivotTable.[30_40_C]+
        PivotTable.[30_40_H]+
        PivotTable.[40_50_Af]+
        PivotTable.[40_50_As]+
        PivotTable.[40_50_C]+
        PivotTable.[40_50_H]
    ) AS Total,
    null as isGrandTotal,
    2 AS sortOrder
FROM
    PivotTable
UNION ALL
SELECT
    PivotTable.city,
    '' AS sex,
    '' AS election,
    SUM(PivotTable.[20_30_Af]) AS [20_30_Af],
    SUM(PivotTable.[20_30_As]) AS [20_30_As],
    SUM(PivotTable.[20_30_C]) AS [20_30_C],
    SUM(PivotTable.[20_30_H]) AS [20_30_H],
    SUM(PivotTable.[30_40_Af]) AS [30_40_Af],
    SUM(PivotTable.[30_40_As]) AS [30_40_As],
    SUM(PivotTable.[30_40_C]) AS [30_40_C],
    SUM(PivotTable.[30_40_H]) AS [30_40_H],
    SUM(PivotTable.[40_50_Af]) AS [40_50_Af],
    SUM(PivotTable.[40_50_As]) AS [40_50_As],
    SUM(PivotTable.[40_50_C]) AS [40_50_C],
    SUM(PivotTable.[40_50_H]) AS [40_50_H],
    SUM(
        PivotTable.[20_30_Af]+
        PivotTable.[20_30_As]+
        PivotTable.[20_30_C]+
        PivotTable.[20_30_H]+
        PivotTable.[30_40_Af]+
        PivotTable.[30_40_As]+
        PivotTable.[30_40_C]+
        PivotTable.[30_40_H]+
        PivotTable.[40_50_Af]+
        PivotTable.[40_50_As]+
        PivotTable.[40_50_C]+
        PivotTable.[40_50_H]
    ) AS Total,
    null as isGrandTotal,
    1 AS sortOrder
FROM
    PivotTable
GROUP BY 
    PivotTable.city
UNION ALL
SELECT
    'Grand Total' AS city,
    '' AS sex,
    '' AS election,
    SUM(PivotTable.[20_30_Af]) AS [20_30_Af],
    SUM(PivotTable.[20_30_As]) AS [20_30_As],
    SUM(PivotTable.[20_30_C]) AS [20_30_C],
    SUM(PivotTable.[20_30_H]) AS [20_30_H],
    SUM(PivotTable.[30_40_Af]) AS [30_40_Af],
    SUM(PivotTable.[30_40_As]) AS [30_40_As],
    SUM(PivotTable.[30_40_C]) AS [30_40_C],
    SUM(PivotTable.[30_40_H]) AS [30_40_H],
    SUM(PivotTable.[40_50_Af]) AS [40_50_Af],
    SUM(PivotTable.[40_50_As]) AS [40_50_As],
    SUM(PivotTable.[40_50_C]) AS [40_50_C],
    SUM(PivotTable.[40_50_H]) AS [40_50_H],
    SUM(
        PivotTable.[20_30_Af]+
        PivotTable.[20_30_As]+
        PivotTable.[20_30_C]+
        PivotTable.[20_30_H]+
        PivotTable.[30_40_Af]+
        PivotTable.[30_40_As]+
        PivotTable.[30_40_C]+
        PivotTable.[30_40_H]+
        PivotTable.[40_50_Af]+
        PivotTable.[40_50_As]+
        PivotTable.[40_50_C]+
        PivotTable.[40_50_H]
    ) AS Total,
    1 as isGrandTotal,
    3 AS sortOrder
FROM
    PivotTable
ORDER BY 
    isGrandTotal,
    city,
    sortOrder

This will definitely work, but I already have the rest of the pivots built out in this way. I thought about it last night and realized I can just create another temp table and then do the Grouping from that table and it worked. http://sqlfiddle.com/#!3/3143e/19 I am going to mark your answer as correct as it will also work. Thanks for your help. — user973671, Apr 11 '12 at 14:13
No problem... Remember to upvote if you think the answer is good. It gives us all warm-fuzzy feelings :P — Arion, Apr 11 '12 at 15:11

Pivot table with rollup and case statement in the Grouping function

1 Answers1