具有两个交叉表和多个列的 SQL 数据透视表

Question

我正在尝试构建一个包含两个交叉表和多列的表格。到目前为止，我有这个，当我运行它时

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

    SELECT  *
    FROM
    (
    SELECT  a.city, a.sex
    FROM [AdventureWorksDW].[dbo].[test2] a

    ) p
    PIVOT
    (
    COUNT (sex)
    FOR sex
    IN ([male], [female], [Unknown])
    ) AS pvt

我明白了

_{(来源:realestateagenthealthinsurance.com)}

但我需要的是这个，顶部有两个交叉选项卡和一个多列选项。

_{(来源:realestateagenthealthinsurance.com)}

这是否可以通过枢轴或任何其他解决方案实现？提前致谢

Answer 1

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 @test2 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