I am trying to create group totals in this pivot table
create table test5(city nvarchar(10), race nvarchar(30), sex nvarchar(10), age int)
insert into test5 values ('Austin', 'African-American', 'male', 21)
insert into test5 values ('Austin', 'Asian', 'female', 22)
insert into test5 values ('Austin', 'Caucasian', 'male', 23)
insert into test5 values ('Austin', 'Hispanic', 'female', 24)
insert into test5 values ('Austin', 'African-American', 'Unknown', 25)
insert into test5 values ('Austin', 'Asian', 'male', 26)
insert into test5 values ('Austin', 'Caucasian', 'female', 27)
insert into test5 values ('Austin', 'Hispanic', 'Unknown', 28)
insert into test5 values ('Austin', 'Asian', 'male', 29)
insert into test5 values ('Austin', 'Caucasian', 'female', 31)
insert into test5 values ('Dallas', 'Hispanic', 'Unknown', 32)
insert into test5 values ('Dallas', 'African-American', 'male', 33)
insert into test5 values ('Dallas', 'Asian', 'female', 34)
insert into test5 values ('Dallas', 'Caucasian', 'Unknown', 35)
insert into test5 values ('Dallas', 'Hispanic', 'male', 500)
insert into test5 values ('Dallas', 'African-American', 'female', 36)
insert into test5 values ('Dallas', 'Asian', 'Unknown', 37)
insert into test5 values ('Dallas', 'Caucasian', 'male', 38)
insert into test5 values ('Dallas', 'Hispanic', 'female', 39)
insert into test5 values ('Dallas', 'African-American', 'Unknown', 41)
insert into test5 values ('Houston', 'Asian', 'male', 42)
insert into test5 values ('Houston', 'Caucasian', 'female', 43)
insert into test5 values ('Houston', 'Hispanic', 'Unknown', 44)
insert into test5 values ('Houston', 'African-American', 'male', 45)
insert into test5 values ('Houston', 'Asian', 'female', 46)
insert into test5 values ('Houston', 'Caucasian', 'Unknown', 47)
insert into test5 values ('Houston', 'Hispanic', 'male', 48)
insert into test5 values ('Houston', 'African-American', 'female', 49)
insert into test5 values ('Houston', 'Asian', 'Unknown', 51)
insert into test5 values ('Houston', 'Caucasian', 'male', 52);
WITH T AS (
SELECT
A.city, A.sex,
CASE
WHEN A.age BETWEEN 20 AND 30 THEN '20-30_' + race
WHEN A.age BETWEEN 31 AND 40 THEN '31-40_' + race
WHEN A.age BETWEEN 41 AND 50 THEN '41-50_' + race
END AS age_range_race
FROM test5 AS A
)
SELECT *
FROM T
PIVOT( COUNT(age_range_race) FOR age_range_race
IN(
[20-30_African-American],
[20-30_Asian],
[20-30_Caucasian],
[20-30_Hispanic],
[31-40_African-American],
[31-40_Asian],
[31-40_Caucasian],
[31-40_Hispanic],
[41-50_African-American],
[41-50_Asian],
[41-50_Caucasian],
[41-50_Hispanic]
)
) AS P
ORDER BY city, sex
I need them to be formatted like this with the group totals on top.
I am going to be putting the pivot in a radgrid, so if there is a way to do it in the grid or with C# that would be good as well. Any suggestions will help as I am looking for basically any way to make this happen. Thanks in advance
You can try using query similar to this:
select city, sex,
count(case when race = 'African-American' and age between 20 and 30 then 1 else null end) as [20_30_African-American],
count(case when race = 'Asian' and age between 20 and 30 then 1 else null end) as [20_30_Asian],
count(case when race = 'Caucasian' and age between 20 and 30 then 1 else null end) as [20_30_Caucasian],
count(case when race = 'Hispanic' and age between 20 and 30 then 1 else null end) as [20_30_Hispanic]
-- add all other columns here
from test5
group by city, sex WITH ROLLUP
order by city, sex