代码之家 › 专栏 › 技术社区 › nir weiner

SQL-Oracle-具有动态数据的数据透视表

views pivot stored-procedures oracle sql

nir weiner · 技术社区 · 7 年前

在Oracle DB上工作时,我有以下“组”表:

ID    NAME    LAYER    VALUE
1     A       L1       100
1     A       L2       200
1     A       L3       300
1     A       L4       400
1     A       L5       500
2     B       L1       111
2     B       L2       222
2     B       L3       333
2     B       L4       444
2     B       L5       555
2     B       L6       666
2     B       L7       777

**ID** - identifies the group.
**NAME** - group's name.
**LAYER** - a layer in the group ; a group consists of N layers.
**VALUE** - the value of a given layer in a given group.

此表中的数据代表2个元素,可通过ID(1和2)区分。每个组包含N个层(对于组1,L1-L5;对于组2,L1-L7),每个层都有一个值。

我正在尝试创建DB功能,它将透视动态层数据,这样每个层将成为一列,它的行将是表中每个唯一id(1,2)的值。

ID    NAME    L1    L2    L3    L4    L5    L6    L7    
1     A       100   200   300   400   500   
2     B       111   222   333   444   555   666   777

请注意,这些组的层数不同。

所说的功能可以是一个视图、一个函数或一个存储过程——实际上是任何东西,只要它是由数据库处理的。

非常感谢你的帮助!

近红外

2 回复 | 直到 7 年前

Wernfried Domscheit 7 年前

您可以构建如下动态查询:

decalre
    sqlstr VARCHAR2(30000);
    cur SYS_REFCURSOR;
begin

    SELECT SELECT LISTAGG(''''||LAYER||''' AS '||layer, ',') WITHIN GROUP (ORDER BY LAYER)
    INTO sqlstr 
    FROM (SELECT LAYER FROM your_table GROUP BY LAYER);

    sqlstr := 'SELECT * FROM your_table PIVOT (MIN(VALUESS) FOR layers IN ('||sqlstr||'))';
    DBMS_OUTPUT.PUT_LINE(sqlstr);

    OPEN cur FOR sqlstr;
    ...

end;

San 7 年前

使用Oracle的PIVOT函数,您可以按如下方式进行操作,您必须将所有30个值的列表放在PIVOT部分,我已经完成了L7:

SQL> 
SQL> WITH cte_table(IDS, NAMES, LAYERS, VALUESS) as (
  2      SELECT 1, 'A', 'L1', 100 from dual union all
  3      SELECT 1, 'A', 'L2', 200 from dual union all
  4      SELECT 1, 'A', 'L3', 300 from dual union all
  5      SELECT 1, 'A', 'L4', 400 from dual union all
  6      SELECT 1, 'A', 'L5', 500 from dual union all
  7      SELECT 2, 'B', 'L1', 111 from dual union all
  8      SELECT 2, 'B', 'L2', 222 from dual union all
  9      SELECT 2, 'B', 'L3', 333 from dual union all
 10      SELECT 2, 'B', 'L4', 444 from dual union all
 11      SELECT 2, 'B', 'L5', 555 from dual union all
 12      SELECT 2, 'B', 'L6', 666 from dual union all
 13      SELECT 2, 'B', 'L7', 777 from dual)
 14  SELECT *
 15    FROM cte_table
 16   PIVOT (MIN(VALUESS) FOR layers IN ('L1' AS "L1", 'L2' AS "L2", 'L3' AS "L3", 'L4' AS "L4", 'L5' AS "L5", 'L6' AS "L6", 'L7' AS "L7")) --list goes here
 17  /

输出:

       IDS NAMES         L1         L2         L3         L4         L5         L6         L7
---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 A            100        200        300        400        500            
         2 B            111        222        333        444        555        666        777