Oracle 11g中单列数据拆分至指定多列的SQL实现问询

假设你的源表为 source_table，其中单列 test_name 存储着每条记录的多行属性数据（每6行对应一条完整记录），目标表为 target_table，包含 NAME、AGE、CITY、NAME_OF_UNI、NAME_OF_SUB、MATCH_PCT、PHONE_NUM 字段。以下是具体实现步骤：

1. 核心SQL语句（含插入逻辑） #

WITH numbered_data AS (
    -- 给每行数据分配分组ID，每6行归为一条完整记录
    SELECT
        test_name,
        CEIL(ROWNUM / 6) AS record_id
    FROM source_table
),
categorized_data AS (
    -- 标记每行数据对应的目标列类型
    SELECT
        record_id,
        CASE
            WHEN test_name LIKE 'Age:%' THEN 'AGE'
            WHEN test_name LIKE 'City%' THEN 'CITY'
            WHEN test_name LIKE 'University%' THEN 'NAME_OF_UNI'
            WHEN test_name LIKE 'Subject%' THEN 'NAME_OF_SUB'
            WHEN test_name LIKE 'Match Percentage%' THEN 'MATCH_PCT'
            WHEN test_name LIKE 'Phone Number%' THEN 'PHONE_NUM'
        END AS col_type,
        test_name AS col_value
    FROM numbered_data
)
-- 将处理后的数据插入目标表
INSERT INTO target_table (NAME, AGE, CITY, NAME_OF_UNI, NAME_OF_SUB, MATCH_PCT, PHONE_NUM)
SELECT
    -- 若NAME字段有专属来源，替换此处逻辑，比如从源表其他列获取
    'Record_' || record_id AS NAME,
    -- 提取年龄数字，转换为数值类型
    TO_NUMBER(REGEXP_SUBSTR(AGE, '\d+')) AS AGE,
    -- 直接取城市信息
    CITY AS CITY,
    -- 直接取大学名称
    NAME_OF_UNI AS NAME_OF_UNI,
    -- 直接取专业名称
    NAME_OF_SUB AS NAME_OF_SUB,
    -- 提取匹配率数字，转换为数值类型
    TO_NUMBER(REGEXP_SUBSTR(MATCH_PCT, '\d+')) AS MATCH_PCT,
    -- 去掉电话号码中的多余引号
    REPLACE(PHONE_NUM, '"', '') AS PHONE_NUM
FROM categorized_data
PIVOT (
    -- 同组内取对应类型的属性值（确保每组仅一条对应类型数据）
    MAX(col_value)
    FOR col_type IN (
        'AGE' AS AGE,
        'CITY' AS CITY,
        'NAME_OF_UNI' AS NAME_OF_UNI,
        'NAME_OF_SUB' AS NAME_OF_SUB,
        'MATCH_PCT' AS MATCH_PCT,
        'PHONE_NUM' AS PHONE_NUM
    )
);

2. 关键逻辑说明 #

• 分组标识：如果源表已有主键或分组字段（比如每条记录有唯一ID），可直接替换 CEIL(ROWNUM/6) 为该字段，无需通过行号分组。
• 类型标记：通过 CASE 语句匹配每行数据的特征，将其映射到目标列类型。
• 行转列：使用Oracle 11g支持的 PIVOT 语法，将同组的多行属性转换为多列。
• 数据清洗：
  • 用 REGEXP_SUBSTR 提取年龄、匹配率中的数字部分，转换为数值类型。
  • 用 REPLACE 去除电话号码中的多余引号。
• 异常处理：若存在数据缺失或格式不符的情况，可添加 NVL 函数设置默认值，比如 NVL(TO_NUMBER(REGEXP_SUBSTR(AGE, '\d+')), 0)。

内容的提问来源于stack exchange，提问作者Md. Sajjad Hussain