时间戳

首页 > 解决方案 > 如何使用 Oracle SQL 11g 创建具有已创建列的动态数据透视表

oracle - 如何使用 Oracle SQL 11g 创建具有已创建列的动态数据透视表

问题描述

在过去的两年中，我在工作中接受了一点 Oracle SQL 培训，这使我在工作中的大部分时间都得到了帮助（除了简单的选择、删除、更新、透视等，这并不是我工作的重要部分）。我最近收到了一个非常复杂的要求，而且远远超出了我的技术能力，不幸的是，我无法获得可用的专业 DBA 的帮助。我需要一个能够恢复正确数据和格式的 Oracle SQL 脚本。

我所做的查询带回了所需的信息：

SELECT T1.ATTRIBUTE_1,
T1.ATTRIBUTE_2,
T1.ATTRIBUTE_3,
T1.COMMON_IDENTIFIER,
T2.ERROR,
T2.STATUS,
T2.TYPE
T2.TYPE_NUMBER
FROM TABLE_1 T1,
TABLE_2 T2
WHERE T1.COMMON_IDENTIFIER = T2.COMMON_IDENTIFIER;

这比我正在寻找的结果要简单得多，但它拥有我需要的所有数据，并且它给出了这样的结果（我正在寻找的结果将由第三张表决定）：

ATTRIBUTE_1   ATTRIBUTE_2 ATTRIBUTE_3 COMMON_IDENTIFER    ERROR   STATUS  TYPE    TYPE_NUMBER
  BLAH          BLAH          BLAH            A1            E       S    TYPE i    #######
  BLAH          BLAH          BLAH            A1            E       S    TYPE ii   #######
  BLAH          BLAH          BLAH            A1            E       S    TYPE iii  #######
  BLAH          BLAH          BLAH            A5            E       S    TYPE i    #######
  BLAH          BLAH          BLAH            A5            E       S    TYPE ii   #######
  BLAH          BLAH          BLAH            A7            E       S    TYPE i    #######
  BLAH          BLAH          BLAH            A1            E       S    TYPE iv   #######

我在互联网上进行了梳理，发现了一个简单的示例，说明了我正在寻找的行为类型：

使用动态 sql 和自定义列名两次透视数据

我试图将它从 SQL 服务器转换为 oracle 时遇到了困难，但它进展缓慢，我最终需要将该查询的结果与另一个表的结果结合起来。

我创建了一个查询，让我接近解决方案，但它缺少给定 TYPE 和相应 TYPE_NUMBER 的 ERROR 和 STATUS 数据：

SELECT * FROM (
   SELECT T1.COMMON_IDENTIFIER,
   T2.TYPE,
   T2.TYPE_NUMBER,
   T2.ERROR,
   T2.STATUS
   FROM TABLE_1 T1, TABLE_2 T2
   WHERE T1.COMMON_IDENTIFIER = T2.COMMON_IDENTIFIER
   AND T2.TYPE IN ('TYPE i', 'TYPE ii', 'TYPE iii', 'TYPE iv'))
PIVOT (
   MAX(TYPE_NUMBER) FOR TYPE IN ('TYPE i', 'TYPE ii', 'TYPE iii', 'TYPE iv')
   )

结果如下所示：

COMMON_IDENTIFIER STATUS  ERROR   TYPE i   TYPE ii  TYPE iii  TYPE iv
        A1          S       E     #######  #######  #######   #######
        A5          S       E     #######  #######      
        A7          S       E     #######

这与我正在寻找的非常接近，但是对于给定的 TYPE 值（TYPE i、TYPE ii 等），我缺少每个唯一 TYPE_NUMBER 的 ERROR 和 STATUS 数据。此外，列标题“TYPE i”、“TYPE ii”等都需要是动态的，因为它们的值将能够在任何给定时间更改并与第三个表相关联。

我需要一个 oracle SQL 查询，它会给我如下所示的结果：

ATTRIBUTE_1    ATTRIBUTE_2    ATTRIBUTE_3     COMMON_IDENTIFIER   ERROR_i  STATUS_i   TYPE_i   ERROR_ii   STATUS_ii   TYPE_ii  ERROR_iii   STATUS_iii  TYPE_iii   etc.                                                                 
  BLAH            BLAH           BLAH               A1               E      S         #######       E          S        #######     E           S        #######    
  BLAH            BLAH           BLAH               A5               E       S        #######       E          S        ####### 
  BLAH            BLAH           BLAH               A7               E       S        #######

关于我正在寻找的结果的一些注释：

- 第一个查询中的 TYPE 列的值并用作我正在查找的第二个查询中的标题，由用户可以访问的第三个表决定，并且可以即时删除或添加值。例如，在第一个查询中，TYPE 列只有 4 个值，这可以随时更改。

- TYPE_NUMBER 列值与 TYPE 值（TYPE i、TYPE ii 等）以及 ERROR 和 STATUS 列的值特别相关。因此，如果用户在第三个表中创建新的 TYPE 值，我需要能够动态创建 TYPE_XX、ERROR_XX 和 STATUS_XX 列以适应更改。

就像我之前所说的那样，超出了我的技术能力，对于任何冗余，我深表歉意。我真的没有任何正式的 SQL 培训，因为它一直在工作，我只是想在描述问题和我正在寻找的解决方案时尽可能清楚。

提前感谢您的任何和所有帮助！

标签： oracleoracle11g

解决方案

distinctERROR和STATUSvalues 可以很容易地完成：

SELECT * FROM (
    SELECT T1.common_identifier, T2.type
         , T2.type_number, T2.error, T2.status
      FROM table_1 T1 INNER JOIN table_2 T2
        ON t1.common_identifier = t2.common_identifier
     WHERE t2.type IN ('TYPE i', 'TYPE ii', 'TYPE iii', 'TYPE iv')
) PIVOT (
    MAX(error) AS error
  , MAX(status) AS status
  , MAX(type_number) AS type
      FOR type IN ( 'TYPE i' AS i, 'TYPE ii' AS ii
                  , 'TYPE iii' AS iii, 'TYPE iv' AS iv )
)

列名会有点奇怪——例如，I_ERROR而不是ERROR_I——但我假设你可以自己解决这个问题。

动态部分更难；PIVOT本身不适用于动态列表；您必须使用动态 SQL（例如，在 PL/SQL 存储过程中）才能完成此操作。

推荐阅读