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
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