如何将SQL表中Data列的键值对拆分为多列并创建视图？

需求说明 #

现有一张包含ID、Name、Data列的表，其中Data列存储多组以分号分隔的键值对。需要创建一个视图，将Data列转换为指定的Age、Sex、Height、Weight列，每行的键值对数量不定（0到4个），列值可能为整数或字符串类型。

原表示例 #

ID   Name      Data
------------------------------------------------
1    John      Age=15;Sex=M;Height=172;Weight=56
2    Jane      Age=20;Height=176;
3    William   Age=32;Sex=M;Weight=77
4    Dan       Age=10
5    Steven    null 

期望视图输出 #

ID   Name      Age   Sex    Height  Weight
------------------------------------------
1    John      15    M      172     56
2    Jane      20    null   176     null 
3    William   32    M      null    77
4    Dan       10    null   null    null
5    Steven    null  null   null    null

不同数据库的SQL实现 #

MySQL #

CREATE VIEW user_info AS
SELECT
    ID,
    Name,
    -- 提取Age字段
    CASE
        WHEN Data LIKE '%Age=%' THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Data, 'Age=', -1), ';', 1))
        ELSE NULL
    END AS Age,
    -- 提取Sex字段
    CASE
        WHEN Data LIKE '%Sex=%' THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Data, 'Sex=', -1), ';', 1))
        ELSE NULL
    END AS Sex,
    -- 提取Height字段
    CASE
        WHEN Data LIKE '%Height=%' THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Data, 'Height=', -1), ';', 1))
        ELSE NULL
    END AS Height,
    -- 提取Weight字段
    CASE
        WHEN Data LIKE '%Weight=%' THEN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Data, 'Weight=', -1), ';', 1))
        ELSE NULL
    END AS Weight
FROM your_table_name; -- 替换为实际表名

SQL Server #

CREATE VIEW user_info AS
SELECT
    ID,
    Name,
    -- 提取Age字段
    CASE
        WHEN Data LIKE '%Age=%' THEN TRIM(SUBSTRING(Data, CHARINDEX('Age=', Data) + 4, CHARINDEX(';', Data + ';', CHARINDEX('Age=', Data)) - CHARINDEX('Age=', Data) - 4))
        ELSE NULL
    END AS Age,
    -- 提取Sex字段
    CASE
        WHEN Data LIKE '%Sex=%' THEN TRIM(SUBSTRING(Data, CHARINDEX('Sex=', Data) + 4, CHARINDEX(';', Data + ';', CHARINDEX('Sex=', Data)) - CHARINDEX('Sex=', Data) - 4))
        ELSE NULL
    END AS Sex,
    -- 提取Height字段
    CASE
        WHEN Data LIKE '%Height=%' THEN TRIM(SUBSTRING(Data, CHARINDEX('Height=', Data) + 7, CHARINDEX(';', Data + ';', CHARINDEX('Height=', Data)) - CHARINDEX('Height=', Data) - 7))
        ELSE NULL
    END AS Height,
    -- 提取Weight字段
    CASE
        WHEN Data LIKE '%Weight=%' THEN TRIM(SUBSTRING(Data, CHARINDEX('Weight=', Data) + 7, CHARINDEX(';', Data + ';', CHARINDEX('Weight=', Data)) - CHARINDEX('Weight=', Data) - 7))
        ELSE NULL
    END AS Weight
FROM your_table_name; -- 替换为实际表名

PostgreSQL #

CREATE VIEW user_info AS
SELECT
    ID,
    Name,
    -- 提取Age字段
    CASE
        WHEN Data LIKE '%Age=%' THEN TRIM(split_part(split_part(Data, 'Age=', 2), ';', 1))
        ELSE NULL
    END AS Age,
    -- 提取Sex字段
    CASE
        WHEN Data LIKE '%Sex=%' THEN TRIM(split_part(split_part(Data, 'Sex=', 2), ';', 1))
        ELSE NULL
    END AS Sex,
    -- 提取Height字段
    CASE
        WHEN Data LIKE '%Height=%' THEN TRIM(split_part(split_part(Data, 'Height=', 2), ';', 1))
        ELSE NULL
    END AS Height,
    -- 提取Weight字段
    CASE
        WHEN Data LIKE '%Weight=%' THEN TRIM(split_part(split_part(Data, 'Weight=', 2), ';', 1))
        ELSE NULL
    END AS Weight
FROM your_table_name; -- 替换为实际表名

说明 #

• 将代码中的your_table_name替换为实际的表名称
• 不同数据库的字符串处理函数存在差异，需根据使用的数据库选择对应代码

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