Сводная таблица SQL с итогом столбцов и строк

В попытке добавить итоговые значения строк и столбцов к этой сводной таблице

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 

Я пытаюсь добиться этого результата

1 http://img853.imageshack.us/img853/8112/sqltotal.jpg

Единственными примерами, которые я могу найти в Google, являются хранимые процедуры, написанные для SQL 2000. Пожалуйста, помогите! заранее спасибо

Здесь вы пытаетесь.

 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 

Пожалуйста, попробуйте как следует.

 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