Oracle Group and Pivot - Oracle中的动态Pivot

Question

我有一个这样的表:

+-----+------+------------+
| SN  | CASE | CASE_VALUE |
+-----+------+------------+
|  A  |  AA  |      1     |
|  A  |  AB  |      5     |
|  A  |  AC  |      3     |
|  A  |  AD  |      4     |
|  B  |  BA  |      5     |
|  B  |  BB  |      7     |
|  B  |  BC  |      5     |
|  B  |  BD  |      1     |
+-----+------+------------+

不确定是否有办法得到

+-----+--------+--------------+--------+--------------+--------+--------------+--------+--------------+
| SN  | CASE_1 | CASE_1_VALUE | CASE_2 | CASE_2_VALUE | CASE_3 | CASE_3_VALUE | CASE_4 | CASE_4_VALUE |
+-----+--------+--------------+--------+--------------+--------+--------------+--------+--------------+
|  A  |   AA   |      1       |   AB   |      5       |   AC   |      3       |   AD   |      4       |
|  B  |   BA   |      5       |   BB   |      7       |   BC   |      5       |   BD   |      1       |
+-----+--------+--------------+--------+--------------+--------+--------------+--------+--------------+

这四种情况没有顺序要求

28/02/2021编辑

如果大小写之间没有顺序?像吹

+-----+------+------------+
| SN  | CASE | CASE_VALUE |
+-----+------+------------+
|  A  |  AB  |      1     |
|  A  |  CD  |      5     |
|  A  |  IJ  |      3     |
|  A  |  GH  |      4     |
|  B  |  OP  |      5     |
|  B  |  EF  |      7     |
|  B  |  MN  |      5     |
|  B  |  KJ  |      1     |
+-----+------+------------+

Answer 1

一种选择是使用条件聚合，以便根据需要进行透视，例如

SELECT sn,
MAX(CASE WHEN SUBSTR(case,2,1) = 'A' THEN case END) AS case_1,
MAX(CASE WHEN SUBSTR(case,2,1) = 'A' THEN case_value END) AS case_1_value,
MAX(CASE WHEN SUBSTR(case,2,1) = 'B' THEN case END) AS case_2,
MAX(CASE WHEN SUBSTR(case,2,1) = 'B' THEN case_value END) AS case_2_value,
MAX(CASE WHEN SUBSTR(case,2,1) = 'C' THEN case END) AS case_3,
MAX(CASE WHEN SUBSTR(case,2,1) = 'C' THEN case_value END) AS case_3_value,
MAX(CASE WHEN SUBSTR(case,2,1) = 'D' THEN case END) AS case_4,
MAX(CASE WHEN SUBSTR(case,2,1) = 'D' THEN case_value END) AS case_4_value                     
FROM t
GROUP BY sn 

在上面的例子中枢轴是静态的。您可以创建一个返回SYS_REFCURSOR类型的函数，如下所示，以便拥有一个动态枢轴

CREATE OR REPLACE FUNCTION Get_Pivoted_Cols RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql       VARCHAR2(32767);
v_cols      VARCHAR2(32767);   
BEGIN
SELECT LISTAGG( 'MAX(CASE WHEN SUBSTR(case,2,1) = '''||cs||''' THEN case END )  AS  "case_'||rn||'",
MAX(CASE WHEN SUBSTR(case,2,1) = '''||cs||''' THEN case_value END )  AS  "case_'||rn||'_value"', ',')
WITHIN GROUP ( ORDER BY rn )                     
INTO v_cols
FROM ( SELECT DISTINCT SUBSTR(case,2,1) AS cs, ROW_NUMBER() OVER (PARTITION BY sn ORDER BY case) AS rn
FROM t );

v_sql :='SELECT sn,'|| v_cols ||' FROM t GROUP BY sn';
OPEN v_recordset FOR v_sql;
RETURN v_recordset;
END;
/

和从SQL Developer的命令行调用，以便查看结果集

VAR rc REFCURSOR
EXEC :rc := Get_Pivoted_Cols;
PRINT rc

Answer 2

您需要pivot行，然后使用decode函数像下面这样映射您的输出。(我希望CASE不是你专栏的真名)。

with your_data (SN, "CASE", CASE_VALUE ) as (
select 'A', 'AA', 1 from dual union all
select 'A', 'AB', 5 from dual union all
select 'A', 'AC', 3 from dual union all
select 'A', 'AD', 4 from dual union all
select 'B', 'BA', 5 from dual union all
select 'B', 'BB', 7 from dual union all
select 'B', 'BC', 5 from dual union all
select 'B', 'BD', 1 from dual
)
select SN
, decode(SN, 'A', CASE_1, CASE_5)CASE_1
, decode(SN, 'A', CASE_1_VALUE, CASE_5_VALUE)CASE_1_VALUE
, decode(SN, 'A', CASE_2, CASE_6)CASE_2
, decode(SN, 'A', CASE_2_VALUE, CASE_6_VALUE)CASE_2_VALUE
, decode(SN, 'A', CASE_3, CASE_7)CASE_3
, decode(SN, 'A', CASE_3_VALUE, CASE_7_VALUE)CASE_3_VALUE
, decode(SN, 'A', CASE_4, CASE_8)CASE_4
, decode(SN, 'A', CASE_4_VALUE, CASE_8_VALUE)CASE_4_VALUE
from your_data t
pivot (
max(case_value) as value, max("CASE") FOR "CASE" in (
'AA' CASE_1
,'AB' CASE_2
,'AC' CASE_3
,'AD' CASE_4
,'BA' CASE_5
,'BB' CASE_6
,'BC' CASE_7
,'BD' CASE_8
)
)
;

Answer 3

我可以得到的最接近，假设(基于您的数据)case中的第一个字符始终与sn匹配，我们可以以第二个字符

为中心

select *
from (
select
data.*
, substr(case, 2, 1) case_t
from data
)
pivot (
max(case) type
, max(case_value) value
for case_t in (
'A' case_1
, 'B' case_2
, 'C' case_3
, 'D' case_4
)
)