如何使用Unpivot在Oracle中将行转换为列，如下所示

Question

源表

成本类别受影响的员工期间成本004000//tr>

Answer 1

使用另一个CTE。您当前的"；解决方案"；在我的查询中是tempCTE；那么提取季度和年度是一项简单的任务。

SQL> WITH
2     a (cost_category,
3        cost_category_type,
4        q1_2020,
5        q2_2020,
6        q3_2020,
7        q4_2020)
8     AS
9        (SELECT 'Employee Impacted', 'Period cost', 10, 20, 0, 4000 FROM DUAL),
10     temp
11     AS
12        (SELECT *
13           FROM a
14                UNPIVOT (VALUE
15                        FOR quarter_year
16                        IN (q1_2020 AS 'Q1-2020',
17                           q2_2020 AS 'Q2-2020',
18                           q3_2020 AS 'Q3-2020',
19                           q4_2020 AS 'Q4-2020')))
20  SELECT t.cost_category,
21         t.cost_category_type,
22         t.quarter_year,
23         SUBSTR (t.quarter_year, 1, 2) quarter,
24         SUBSTR (t.quarter_year, 4) year,
25         t.VALUE
26    FROM temp t
27  /
COST_CATEGORY     COST_CATEGO QUARTER QUARTER  YEAR      VALUE
----------------- ----------- ------- -------- ---- ----------
Employee Impacted Period cost Q1-2020 Q1       2020         10
Employee Impacted Period cost Q2-2020 Q2       2020         20
Employee Impacted Period cost Q3-2020 Q3       2020          0
Employee Impacted Period cost Q4-2020 Q4       2020       4000
SQL>