SQL pivot table with column and row totals

Question

In am trying to add row and column totals to this pivot table

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

            WITH T AS (
            SELECT 
                  A.city as city, A.sex as 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 test4 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

I am trying to achieve this result

The only examples I can find on Google are stored procedures written for SQL 2000. Please help! Thanks in advance

possible duplicate of [Can you subtotal rows and/or columns in a pivot table?](http://stackoverflow.com/questions/7925748/can-you-subtotal-rows-and-or-columns-in-a-pivot-table) — bummi, Dec 01 '14 at 09:31

score 9 · Accepted Answer · answered Mar 07 '12 at 05:09

Here you try.

WITH T AS (
            SELECT 
                  A.city as city, A.sex as 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 #test4 AS A
           )


SELECT  *, ([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]) Total
into #tmp_result
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

select * 
from #tmp_result
union all
select 'Grand Total','',SUM([20-30_African-American]), SUM([20-30_Asian]), SUM([20-30_Caucasian]), SUM([20-30_Hispanic]),SUM([31-40_African-American]), SUM([31-40_Asian]), SUM([31-40_Caucasian]), SUM([31-40_Hispanic]),SUM([41-50_African-American]), SUM([41-50_Asian]), SUM([41-50_Caucasian]),SUM([41-50_Hispanic]), sum(Total)
from #tmp_result

its really simple and good, but can we modify it parametric? for able to use with dynamic columns. — Rapunzo, Mar 06 '13 at 09:33

score 2 · Answer 2 · answered Mar 07 '12 at 03:32

Please try as follow..

WITH T AS (
            SELECT 
                  A.city as city, A.sex as 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 #test4 AS A
           )


SELECT  *, ([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]) Total
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

This is good, but I also need a total for columns in the bottom if possible, Thanks — user973671, Mar 07 '12 at 03:42
Then you need to keep these result into temp table. After that, make is union script.. select 'Total','', sum([20-30_African-American]), etc — Thit Lwin Oo, Mar 07 '12 at 03:48
Can you provide an example? I am sorry I don't understand it fully. Thanks — user973671, Mar 07 '12 at 03:57

SQL pivot table with column and row totals

2 Answers2

Linked