我有下表TEMP
我想使用 SQL 创建一个数据透视视图,排序依据CATEGORY
ASC,通过LEVEL
降序和SET
ASC 并填写value
.
预期输出:
我已尝试以下代码,但无法解决引发错误的聚合部分:
SELECT *
FROM
(SELECT
SET, LEVEL, CATEGORY, VALUE
FROM
TEMP
ORDER BY
CATEGORY ASC, LEVEL DESC, SET ASC) x
PIVOT
(value(VALUE) FOR RISK_LEVEL IN ('X','Y','Z') AND CATEGORY IN ('ABC', 'DEF', 'GHI', 'JKL')) p
此外,我想知道是否有任何方法可以动态添加列并为具有相同列的任何表提供此视图(以便可以避免硬编码)。
我知道我们可以在 Excel 中执行此操作并转置它,但我希望数据以这种格式存储在数据库中。
存储的函数(或程序) 可能会被创建,以便为动态透视创建 SQL,并将结果集加载到 类型的变量中SYS_REFCURSOR
:
CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_cols_1 VARCHAR2(32767);
v_cols_2 VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''''||"level"||''' AS "'||"level"||'"' , ',' )
WITHIN GROUP ( ORDER BY "level" DESC )
INTO v_cols_1
FROM (
SELECT DISTINCT "level"
FROM temp
);
SELECT LISTAGG( 'MAX(CASE WHEN category = '''||category||''' THEN "'||"level"||'" END) AS "'||"level"||'_'||category||'"' , ',' )
WITHIN GROUP ( ORDER BY category, "level" DESC )
INTO v_cols_2
FROM (
SELECT DISTINCT "level", category
FROM temp
);
v_sql :=
'SELECT "set", '|| v_cols_2 ||'
FROM
(
SELECT *
FROM temp
PIVOT
(
MAX(value) FOR "level" IN ( '|| v_cols_1 ||' )
)
)
GROUP BY "set"
ORDER BY "set"';
OPEN v_recordset FOR v_sql;
RETURN v_recordset;
END;
其中我使用了两个级别的旋转:第一个是在涉及的内部查询中PIVOT
子句,第二个是在具有条件聚合逻辑的外部查询中。请注意,级别的顺序应按降序排列(Z
, Y
, X
)在符合描述的预期结果内。
然后调用
VAR rc REFCURSOR
EXEC :rc := Get_Categories_RS;
PRINT rc
从 SQL Developer 的命令行获取结果集
顺便说一句,避免使用保留关键字,例如set
and level
就像你的情况一样。我需要引用它们才能使用。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)