Oracle：将名称值对扁平化到表中

Question

我希望有人可以提供一些建议，以更简单的方法来处理这个问题。 我正在致力于创建高度标准化的数据集的扁平化视图。 扁平化的目标是提供一个普通人可以用来开发报告的视图。 源数据包含几个表，如下所示：

CREATE TABLE VARIABLES  ( 
    VARIABLE_ID INT NOT NULL IDENTITY, 

    VARIABLE_NAME VARCHAR(100) NOT NULL, 

    VARIABLE_DATATYPE VARCHAR(100) NOT NULL, 

    PRIMARY KEY (VARIABLE_ID), 

    UNIQUE (VARIABLE_NAME,VARIABLE_DATATYPE) 
)

CREATE TABLE RECORD_VALUES (
    RUN_ID INT NOT NULL REFERENCES RUNS (RUN_ID) ON DELETE CASCADE, 

    VARIABLE_ID INT NOT NULL REFERENCES VARIABLES(VARIABLE_ID) ON DELETE CASCADE, 

    RECORD_ID VARCHAR(100) NOT NULL, 

    VARIABLE_VALUE VARCHAR(1000), 

    PRIMARY KEY (RUN_ID,VARIABLE_ID,RECORD_ID)
)

记录值表中的variable_id 对应于原始输入流中的变量之一，例如地址或帐户余额。 对于包含 12 个变量的输入记录，记录值表中将有 12 行。

原始过程的输入包括不同宽度和变量名称的记录。 它们被分成 record_values 表中的名称/值元组。 我正在编写一个过程，将变量重新组装回记录，看起来像

run_id
record_id (which is actually an underlying account number)
variable_value_1
variable_value_2
...
variable_value_n

我当前的方法是通过查找给定运行集的唯一变量来动态构建表（细节在这里并不重要），然后构建一个 SQL 字符串将创建该表。

我的挑战是如何有效地从原始数据加载结果工作表。 由于变量的名称和数量随 run_id 的不同而变化，我能想到的解决此问题的唯一方法是通过以下方式：

create a cursor for the list of variables
for each variable in the list
   create a cursor to find all the record values for that variable
   for each record value
       update the appropriate record/column in the work table
   end
end

这将永远运行，因为父表有数百百万行。

有谁知道如何生成一种可用于对每个目标行进行一次更新的方法？

以免有人跳到原始表格的设计上 - 这样做是有商业原因的。 我不喜欢这样做，但这样做有充分的理由。

感谢您提供的任何想法。

安德鲁

Answer 1

在 Oracle 10g 及更高版本中：

SELECT  DISTINCT run_id, record_id, val1, val2, ..., val12
FROM    record_values
MODEL
IGNORE NAV
PARTITION BY
        (run_id, record_id)
DIMENSION BY
        (variable_id)
MEASURES
        (val, 0 AS val1, 0 AS val2, ..., 0 AS val12)
RULES UPDATE
        (
        val1[ANY] = val[1], /* Put real variable ID's in the square brackets */
        val2[ANY] = val[2],
        ...,
        val12[ANY] = val[12]
        )