Search code examples
c#sql-server-2008radgridpivot-table

Group totals in a sql pivot table


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.

1

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


Solution

  • 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