Oracle条件数据行列转换：基于源表向目标表B插入数据

Got it, let's work through converting your source table's data into target table B using Oracle. First, I'll assume a logical structure for B (since you didn't specify it) — if your actual target table is different, you can tweak the field names accordingly.

Assumed Target Table B Structure #

First, let's define a reasonable B table that fits your source data:

CREATE TABLE B (
    ID NUMBER,
    NAME VARCHAR2(50),
    CUSTOMERID NUMBER,
    CUSTOMER_NAME VARCHAR2(50),
    WORK_PHONE VARCHAR2(20),
    MOBILE_PHONE VARCHAR2(20),
    FAX_PHONE VARCHAR2(20)
);

This structure groups each customer with all the phone types associated with their linked user (so one row per customer, with all relevant phone numbers as columns).

Solution 1: Use PIVOT (Recommended, Clean Syntax) #

If you're on Oracle 11g or newer, the PIVOT clause is the easiest way to handle this row-to-column shift. Here's how to insert the transformed data into B:

INSERT INTO B (ID, NAME, CUSTOMERID, CUSTOMER_NAME, WORK_PHONE, MOBILE_PHONE, FAX_PHONE)
SELECT ID, NAME, CUSTOMERID, CUSTOMER_NAME, WORK, MOBILE, FAX
FROM (
    -- First, normalize the source data (fix that space in the customer name column)
    SELECT 
        ID, 
        NAME, 
        PHONETYPE, 
        PHONENO, 
        CUSTOMERID, 
        "CUSTOMER NAME" AS CUSTOMER_NAME
    FROM your_source_table  -- Replace this with your actual source table name
)
PIVOT (
    MAX(PHONENO)  -- Since each (ID, NAME, CUSTOMERID, PHONETYPE) has one number, MAX/MIN works
    FOR PHONETYPE IN ('Work' AS WORK, 'Mobile' AS MOBILE, 'Fax' AS FAX)
);

COMMIT; -- Don't forget to save the changes!

Breakdown of the Code: #

• The inner subquery cleans up the source data: it renames the space-containing CUSTOMER NAME column to CUSTOMER_NAME for easier handling, and pulls all the fields we need.
• The PIVOT clause takes the distinct PHONETYPE values and turns them into columns, using MAX(PHONENO) to grab the corresponding phone number for each group. Since each group only has one number, MAX is just a way to aggregate without changing the value.
• Finally, we insert the transformed result straight into table B.

Solution 2: CASE + Aggregation (For Older Oracle Versions) #

If you're stuck on Oracle 10g or earlier (no PIVOT support), use CASE statements paired with aggregation functions to get the same result:

INSERT INTO B (ID, NAME, CUSTOMERID, CUSTOMER_NAME, WORK_PHONE, MOBILE_PHONE, FAX_PHONE)
SELECT 
    ID,
    NAME,
    CUSTOMERID,
    "CUSTOMER NAME" AS CUSTOMER_NAME,
    MAX(CASE WHEN PHONETYPE = 'Work' THEN PHONENO END) AS WORK_PHONE,
    MAX(CASE WHEN PHONETYPE = 'Mobile' THEN PHONENO END) AS MOBILE_PHONE,
    MAX(CASE WHEN PHONETYPE = 'Fax' THEN PHONENO END) AS FAX_PHONE
FROM your_source_table  -- Replace with your actual source table name
GROUP BY ID, NAME, CUSTOMERID, "CUSTOMER NAME";

COMMIT;

Breakdown of the Code: #

• Each CASE statement checks the PHONETYPE and returns the corresponding PHONENO (or NULL if it doesn't match).
• MAX() aggregates these values per group (grouped by ID, NAME, CUSTOMERID, and customer name), which collapses multiple rows for the same customer into one row with all phone columns filled in.
• The GROUP BY ensures we get one row per customer, as intended.

Expected Result in Table B #

After running either solution, table B will have this data:

Ryan doesn't have a Fax number in the source data, so that column shows NULL — which is exactly what we expect.

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