具有列和行总计的SQL数据透视表 [英] SQL pivot table with column and row totals

问题描述

正在尝试向此数据透视表添加行和列总计

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

我可以在Google上找到的唯一示例是为SQL 2000编写的存储过程.请帮忙！预先感谢

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

推荐答案

在这里尝试.

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

这篇关于具有列和行总计的SQL数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！