SQL tableau croisé dynamique avec colonne et de la ligne des totaux

Dans suis en train d'ajouter de ligne et de colonne des totaux de ce tableau croisé dynamique

      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

Je suis en train d'essayer de parvenir à ce résultat

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

Les seuls exemples que je peux trouver sur Google sont stockées procédures écrites pour SQL 2000. S'il vous plaît aider! Merci d'avance

Votre script est au travail, droit?
oui, j'essaie simplement de créer des totaux.
double possible de Pouvez-vous sous-total des lignes et/ou colonnes dans un tableau croisé dynamique?

OriginalL'auteur user973671 | 2012-03-07

  1. 8

    Ici, vous essayez.

    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
    C'est parfait, Merci beaucoup.
    c'est vraiment simple et bon, mais peut-on le modifier, il paramétrique? pour pouvoir l'utiliser avec des colonnes dynamiques.

    OriginalL'auteur Thit Lwin Oo

  2. 2

    S'il vous plaît essayer à suivre..

    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
    C'est bon, mais j'ai aussi besoin d'un total de colonnes dans le fond si possible, Merci
    Ensuite, vous avez besoin de garder ces résultat dans la table temporaire. Après cela, faire de l'union est-script.. sélectionnez 'Total',", sum([20-30_African-Américaine]), etc
    Pouvez-vous me donner un exemple? Je suis désolé, je ne comprends pas complètement. Merci

    OriginalL'auteur Thit Lwin Oo