如何在PL/SQL中生成符合NIH格式的三维透视表？

我需要生成一张符合美国国立卫生研究院（NIH）格式的表格，但在PL/SQL中实现三维数据表时遇到了困难。最初我只有处理种族与族裔数据的透视表代码：

total_demographics as
(
    SELECT 
        ETHNICITY, 
        RACE,
        race_number
    FROM study_demographics
    UNION ALL
    SELECT 
        'Total' as ETHNICITY, 
        RACE,
        ethnicity_number
    FROM study_demographics
    UNION ALL
    SELECT 
        ETHNICITY,
        'Total' as RACE, 
        race_number
    FROM study_demographics
    UNION ALL
    SELECT 
        'Total' as ETHNICITY,
        'Total' as RACE, 
        row_number
    FROM study_demographics
 )
SELECT
    * 
FROM
    (
        SELECT 
            ETHNICITY,
            RACE
        FROM
            total_demographics
    )
PIVOT
    (
        COUNT(RACE)
        FOR (RACE) IN (
            'Not Reported',
            'Other',
            'American Indian', 
            'Asian',
            'Black',
            'Declined',
            'More than one race',
            'Native Hawaiian or other Pacific Islander',
            'Unknown',
            'White',
            'Total'
            )
    )
ORDER BY (
    case ETHNICITY
        when 'Missing' then 0
        when 'Declined' then 1
        when 'Hispanic or Latino' then 2
        when 'Non-Hispanic' then 3
        when 'Unknown' then 4
        when 'Total' then 5
        else
            6
    end
            )

我从未制作过三维表格，但可以生成性别相关信息并整合到PL/SQL中。最终通过多列PIVOT的方式实现了需求，核心是在PIVOT的FOR子句中处理性别与族裔的组合结果及各类总计，完整实现代码如下：

study_demographics AS
(
  SELECT  study_number, 
    ROW_NUMBER() OVER (PARTITION BY ETHNICITY, RACE, GENDER ORDER BY ETHNICITY, RACE, GENDER) AS line_number,
    ROW_NUMBER() OVER (PARTITION BY ETHNICITY ORDER BY RACE ASC) AS ethnicity_number,
    ROW_NUMBER() OVER (PARTITION BY RACE ORDER BY ETHNICITY, GENDER ASC) AS race_number,
    ROW_NUMBER() OVER (PARTITION BY GENDER ORDER BY ETHNICITY, GENDER ASC) AS gender_number,
    ETHNICITY,
    RACE,
    GENDER
 FROM
    total_study_demographics
 ),
 total_demographics as
 (
 SELECT DISTINCT
    ETHNICITY, 
    GENDER,
    RACE,
    line_number
FROM study_demographics
UNION ALL
SELECT DISTINCT
    'Total' as ETHNICITY, 
    GENDER as GENDER,
    RACE as RACE, 
    race_number
FROM study_demographics
UNION ALL
SELECT DISTINCT
    ETHNICITY as ETHNICITY,
    GENDER as GENDER,
    'Total' as RACE, 
    gender_number
FROM study_demographics
UNION ALL
SELECT DISTINCT
    ETHNICITY as ETHNICITY,
    'Total' as GENDER, 
    RACE,
    race_number
FROM study_demographics
UNION ALL
SELECT
    'Total' as ETHNICITY,
    'Total' as GENDER,
    'Total' as RACE, 
    line_number
FROM study_demographics
)
SELECT 
 *
FROM
(
SELECT
        RACE, MNH_G, FNH_G, UNH_G, MH_G, FH_G, UH_G, MU_G, FU_G, UU_G, (TNH_G + THL_G + TU_G + TOTAL_G) AS TOTAL
 FROM
 (
    SELECT
        *
    FROM
        total_demographics

 PIVOT
 (
    COUNT(line_number) as G
    FOR (GENDER, ETHNICITY) IN (
        ('Male','Non-Hispanic') AS MNH,
        ('Female','Non-Hispanic') AS FNH,
        ('Unknown','Non-Hispanic') AS UNH,
        ('Male','Hispanic or Latino') AS MH,
        ('Female','Hispanic or Latino') AS FH,
        ('Unknown','Hispanic or Latino') AS UH,
        ('Male','Unknown') AS MU,
        ('Female','Unknown') AS FU,
        ('Unknown','Unknown') AS UU,
        ('Total','Non-Hispanic') AS TNH,
        ('Total','Hispanic or Latino') AS THL,
        ('Total','Unknown') AS TU,
        ('Total','Total') AS Total
        )       
)
ORDER BY (
case RACE
    when 'American Indian' then 0
    when 'Asian' then 1
    when 'Native Hawaiian or other Pacific Islander' then 2
    when 'Black' then 3
    when 'White' then 4
    when 'More than one race' then 5
    when 'Unknown' then 6
    when 'Total' then 7
    else
        8
 END
        )
        )
 )

生成的表格在SQL Developer中显示效果符合NIH格式要求。

内容的提问来源于stack exchange，提问作者Timothy Dooling