Oracle枢轴-一个表字段分裂成行和列。

Question

我有一个Oracle 12c表，如下所示：

Name     Class        Type 
--------------------------
Alice    Math           S
Alice    Female         A
Bob      Anthropology   S  
Bob      Male           A
Charlie  Science        S
Charlie  Tennis         A
Charlie  Male           A
Do       Math           S
Do       Female         A
Do       Tennis         A
Elmer    Male           A
Elmer    Science        S

我想把这张表转到下面来编写一份报告。类型"S“的类是行，键入"A”是列。然后计算属于每一类的人数。例如，有两个人都是女性，参加数学课程--爱丽丝和Do。

              Female    Male    Tennis   Total
-----------------------------------------------
Math            2         0        1       3
Anthropology    0         1        0       1 
Science         0         2        1       3
Total           2         3        2       7

我熟悉Oracle中的pivot和多维数据集，但我一直忙于在报表中的行和列之间分割Class字段。

我非常感谢你的帮助。

Answer 1

您可以按以下方式使用条件聚合：

SQL> (名称、类别、类型)为2(3选择“Alice”、“Math”、“S”从双重结合中选择“Alice”、“with”、“A”来自双重联合，所有5个选择“Bob”、“人类学”、“S”，从双重联合中选择“Bob”、“男性”、“A”)。“科学”、“S”从双重结合中选择“Charlie”、“网球”、“A”从双重结合中选择“Charlie”、“男性”、“A”从双重结合中全部选择‘Do’、'Math‘、'S’从双重结合中选择‘Do’、‘女性’、'A‘从双重结合中选择’Do‘全部12个选择'Do'，‘网球’，'A‘来自双重联合，所有13个选择'Elmer'，’男性‘，'A’从双重结合中选择'Elmer'，'Science‘，'S’从Dual15开始，16 --您的查询从这里开始，17个CTE从18开始( 19选择S.CLASS作为类，20个合并(当A.CLASS =‘女性’1结束时)，0作为女性，21次合并( A.CLASS =“男性”然后1结束)，0次合并为男性，22次合并( A.CLASS =“网球”然后1结束)，0合并为网球，23次合并( A.CLASS IN (“女性”，“男性”，“网球”)，1结束)，( 0)在A.name= 'S‘和A.TYPE = 'A’26组中，S.type =‘S’和A.TYPE=‘A’26组由S.CLASS 27选择28级，女性、男性、网球、来自CTE 29联盟的共计30人选择“总计”作为级别，SUM(女性)、SUM(男性)、SUM(网球)、SUM(总计)；男网球全班2 0 1 3合计2 3 2 7 SQL>

干杯！！

Answer 2

目标“行”值和目标“列”值分别位于不同的行中。为了按多维数据集进行分组，需要使用联接将它们放在同一个输入行中。所以做一个连接，然后是一个立方体组，然后是一个枢轴。为了保持正确，可以使用分组函数来查看何时对“行”进行分组，以及何时对“列”进行分组。这就成了很多工作，你最终会在一些你想要零的地方得到零。以下是满足好奇心的解决方案：

with data(name, class, type) as (
  select 'Alice',    'Math'           ,'S' from dual union all
  select 'Alice',    'Female'         ,'A' from dual union all
  select 'Bob',      'Anthropology'   ,'S' from dual union all
  select 'Bob',      'Male'           ,'A' from dual union all
  select 'Charlie',  'Science'        ,'S' from dual union all
  select 'Charlie',  'Tennis'         ,'A' from dual union all
  select 'Charlie',  'Male'           ,'A' from dual union all
  select 'Do',       'Math'           ,'S' from dual union all
  select 'Do',       'Female'         ,'A' from dual union all
  select 'Do',       'Tennis'         ,'A' from dual union all
  select 'Elmer',    'Male'           ,'A' from dual union all
  select 'Elmer',    'Science'        ,'S' from dual
)
, joined_data as (
  select s.class row_class, a.class col_class
  from data s
  join data a 
  on s.type = 'S' and a.type = 'A' and s.name = a.name
)
, cubed_data as (
  select case grouping(col_class) when 1 then 'Total' else col_class end col_class,
    row_class, grouping(row_class) gr_row, count(*) cnt
  from joined_data
  group by cube(row_class, col_class)
) 
select case gr_row when 1 then 'Total' else row_class end " ",
  "Female", "Male", "Tennis", "Total"
from cubed_data
pivot(  
  sum(cnt) for col_class in (
    'Female' as "Female",'Male' as "Male",'Tennis' as "Tennis",'Total' as "Total"
  )  
)  
order by gr_row, row_class;

                 Female       Male     Tennis      Total
------------ ---------- ---------- ---------- ----------
Anthropology                     1                     1
Math                  2                     1          3
Science                          2          1          3
Total                 2          3          2          7

你好，斯图

Answer 3

对于这个需求，我更喜欢Tejash的解决方案，而不是我的另一个答案中的PIVOT演示。然而，我建议使用汇总而不是UNION。两行改变了，工会全部被排除在外。

with data(name, class, type) as (
  select 'Alice',    'Math'           ,'S' from dual union all
  select 'Alice',    'Female'         ,'A' from dual union all
  select 'Bob',      'Anthropology'   ,'S' from dual union all
  select 'Bob',      'Male'           ,'A' from dual union all
  select 'Charlie',  'Science'        ,'S' from dual union all
  select 'Charlie',  'Tennis'         ,'A' from dual union all
  select 'Charlie',  'Male'           ,'A' from dual union all
  select 'Do',       'Math'           ,'S' from dual union all
  select 'Do',       'Female'         ,'A' from dual union all
  select 'Do',       'Tennis'         ,'A' from dual union all
  select 'Elmer',    'Male'           ,'A' from dual union all
  select 'Elmer',    'Science'        ,'S' from dual
)
SELECT case grouping(S.CLASS) when 1 then 'Total' else s.class end AS CLASS,
COALESCE(SUM(CASE WHEN A.CLASS = 'Female' THEN 1 END),0) AS Female,
COALESCE(SUM(CASE WHEN A.CLASS = 'Male' THEN 1 END),0) AS Male,
COALESCE(SUM(CASE WHEN A.CLASS = 'Tennis' THEN 1 END),0) AS Tennis,
count(*) AS TOTAL
FROM DATA S JOIN DATA A  ON (A.name = S.name)
WHERE S.type = 'S' AND A.TYPE = 'A'
GROUP BY rollup(S.CLASS);

CLASS            FEMALE       MALE     TENNIS      TOTAL
------------ ---------- ---------- ---------- ----------
Anthropology          0          1          0          1
Math                  2          0          1          3
Science               0          2          1          3
Total                 2          3          2          7

你好，斯图