从 SQL 表在 SQL 中创建数据透视视图 [英] Create Pivot view in SQL from a SQL table

问题描述

我有下表TEMP

我想使用 SQL 创建一个透视视图，按 CATEGORY ASC 排序，按 LEVEL DESC 和 SET ASC 并填写 <代码>值 .

I want to create a pivot view using SQL, Ordered by CATEGORY ASC ,by LEVEL DESC and SET ASC and fill in the value .

预期输出:

我尝试了以下代码，但无法解决引发错误的聚合部分:

I have tried the following code but unable to get a workaround the aggregate part which is throwing an error:

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

此外，我想知道是否有任何方法可以动态添加列并为具有相同列的任何表到达此视图(这样可以避免硬编码).

Furthermore I want to know if there can be any method for dynamically adding the columns and arriving at this view for any table having the same columns (so that hardcoding can be avoided).

我知道我们可以在 Excel 中执行此操作并将其转置，但我希望数据以这种格式存储在数据库中.

I know we can do this in Excel and transpose it, but I want the data to be stored in the db in this format.

推荐答案

可能会创建存储函数(或过程)以创建用于动态透视的 SQL，并加载结果集进入 SYS_REFCURSOR 类型的变量:

A stored function(or procedure) might be created in order to create a SQL for Dynamic Pivoting, and the result set is loaded into a variable of type 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).

in which I used two levels of pivoting : the first is within the inner query involving PIVOT Clause, and the second is in the outer query having the conditional aggregation logic. Notice that the order of levels should be in the descending order( Z, Y, X ) within the expected result as conforming to the description.

然后调用

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

从 SQL Developer 的命令行获取结果集

from SQL Developer's Command Line in order to get the result set

顺便说一句，在您的情况下，请避免使用 set 和 level 等保留关键字.我需要引用它们才能使用.

Btw, avoid using reserved keywords such as set and level as in your case. I needed to quote them in order to be able to use.

这篇关于从 SQL 表在 SQL 中创建数据透视视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！