Oracle：如何将多行键值动态列表迁移为固定列结构表？

Alright, let's tackle this multi-row to single-row transformation in Oracle—including handling those unplanned key-value pairs in the new DYNAMICS column. I'll walk you through concrete examples and explain the logic step by step.

First, let's define example table structures to make this tangible (adjust names to match your actual tables):

1. Example Table Definitions #

Source Table (Dynamic Key-Value Rows) #

This is your original table grouped by CODE, with dynamic key-value pairs stored across multiple rows:

CREATE TABLE SOURCE_KEY_VALUE (
    CODE VARCHAR2(50) NOT NULL,
    KEY_NAME VARCHAR2(100) NOT NULL,
    KEY_VALUE VARCHAR2(2000),
    PRIMARY KEY (CODE, KEY_NAME)
);

Target Table (Single-Row with Fixed Columns + Dynamic Storage) #

This is your destination table, with fixed columns for known keys and a DYNAMICS column to capture unknown ones. We'll use Oracle's native JSON type here—it’s perfect for flexible, unstructured key-value data:

CREATE TABLE TARGET_FIXED_COL (
    CODE VARCHAR2(50) PRIMARY KEY,
    -- Fixed columns mapped from known KEY_NAME values in the source
    FIXED_KEY1 VARCHAR2(2000),
    FIXED_KEY2 VARCHAR2(2000),
    -- Column to store unknown key-value pairs
    DYNAMICS JSON
);

2. Core SQL to Transform and Insert Data #

The solution combines pivoting for known fixed keys and JSON aggregation for unknown keys. Here's the full insert statement:

INSERT INTO TARGET_FIXED_COL (CODE, FIXED_KEY1, FIXED_KEY2, DYNAMICS)
WITH source_agg AS (
    SELECT
        CODE,
        -- Pivot known keys into dedicated fixed columns
        MAX(CASE WHEN KEY_NAME = 'FIXED_KEY1' THEN KEY_VALUE END) AS FIXED_KEY1,
        MAX(CASE WHEN KEY_NAME = 'FIXED_KEY2' THEN KEY_VALUE END) AS FIXED_KEY2,
        -- Aggregate ALL key-value pairs into a single JSON object
        JSON_OBJECTAGG(KEY_NAME VALUE KEY_VALUE) AS ALL_KEYS
    FROM SOURCE_KEY_VALUE
    GROUP BY CODE
)
SELECT
    CODE,
    FIXED_KEY1,
    FIXED_KEY2,
    -- Strip known keys from the JSON object to keep only unknowns
    JSON_REMOVE(ALL_KEYS, '$.FIXED_KEY1', '$.FIXED_KEY2') AS DYNAMICS
FROM source_agg;
COMMIT;

Logic Breakdown: #

• CTE source_agg:
  • Uses MAX(CASE...) to pivot specific known KEY_NAME values into their own columns (this works because each CODE+KEY_NAME pair is unique in the source table).
  • Uses JSON_OBJECTAGG to collect every key-value pair for each CODE into a single JSON object.
• Final SELECT:
  • Uses JSON_REMOVE to eliminate the known fixed keys from the JSON object, leaving only the unknown pairs in DYNAMICS. If there are no unknown keys, DYNAMICS will default to an empty JSON object {}.

3. Alternative: Using Oracle's Explicit PIVOT Clause #

If you prefer Oracle's built-in PIVOT syntax over CASE statements, here's an equivalent version:

INSERT INTO TARGET_FIXED_COL (CODE, FIXED_KEY1, FIXED_KEY2, DYNAMICS)
WITH pivoted_data AS (
    SELECT *
    FROM SOURCE_KEY_VALUE
    PIVOT (
        MAX(KEY_VALUE) FOR KEY_NAME IN ('FIXED_KEY1' AS FIXED_KEY1, 'FIXED_KEY2' AS FIXED_KEY2)
    )
),
all_keys_agg AS (
    SELECT CODE, JSON_OBJECTAGG(KEY_NAME VALUE KEY_VALUE) AS ALL_KEYS
    FROM SOURCE_KEY_VALUE
    GROUP BY CODE
)
SELECT
    p.CODE,
    p.FIXED_KEY1,
    p.FIXED_KEY2,
    JSON_REMOVE(a.ALL_KEYS, '$.FIXED_KEY1', '$.FIXED_KEY2') AS DYNAMICS
FROM pivoted_data p
JOIN all_keys_agg a ON p.CODE = a.CODE;
COMMIT;

4. Edge Case Considerations #

• If a CODE has no value for a fixed key, the corresponding column will be NULL—use NVL() to set a default value if needed.
• If all keys for a CODE are unknown, the fixed columns will be NULL and DYNAMICS will contain all key-value pairs.
• To update existing rows instead of inserting new ones, replace INSERT INTO with MERGE INTO to handle upserts seamlessly.

内容的提问来源于stack exchange，提问作者Alessandro C