如何使用Python或T-SQL动态拆分Details列为多列？

T-SQL 实现方法 #

针对SQL Server环境，可通过动态SQL结合拆分函数实现动态列拆分：

步骤1：提取所有唯一键值 #

先拆分Details列的键值对，收集所有可能的键作为后续动态列的基础：

-- 临时表存储拆分后的键值对
DROP TABLE IF EXISTS #KeyValuePairs;
CREATE TABLE #KeyValuePairs (
    ID INT,
    KeyName NVARCHAR(100),
    KeyValue NVARCHAR(MAX)
);

-- 拆分Details列的键值对
INSERT INTO #KeyValuePairs (ID, KeyName, KeyValue)
SELECT 
    t.ID,
    TRIM(LEFT(s.value, CHARINDEX(':', s.value) - 1)) AS KeyName,
    TRIM(SUBSTRING(s.value, CHARINDEX(':', s.value) + 1, LEN(s.value))) AS KeyValue
FROM YourTableName t
CROSS APPLY STRING_SPLIT(ISNULL(t.Details, ''), ';') s
WHERE s.value <> '' AND CHARINDEX(':', s.value) > 0;

-- 收集所有唯一键
DECLARE @Columns NVARCHAR(MAX);
SELECT @Columns = STRING_AGG(QUOTENAME(KeyName), ', ')
FROM (SELECT DISTINCT KeyName FROM #KeyValuePairs) AS Keys;

步骤2：动态生成透视查询 #

利用PIVOT将键值对转换为列，生成最终结果：

DECLARE @DynamicSQL NVARCHAR(MAX);
SET @DynamicSQL = N'
SELECT 
    t.ID,
    t.Details,
    ' + @Columns + '
FROM YourTableName t
LEFT JOIN (
    SELECT ID, ' + @Columns + '
    FROM #KeyValuePairs
    PIVOT (
        MAX(KeyValue)
        FOR KeyName IN (' + @Columns + ')
    ) AS PivotTable
) p ON t.ID = p.ID
ORDER BY t.ID;';

EXEC sp_executesql @DynamicSQL;

DROP TABLE IF EXISTS #KeyValuePairs;

注意替换代码中的YourTableName为实际数据表名称。

Python（Pandas）实现方法 #

使用Pandas可简洁实现动态列拆分：

import pandas as pd

# 读取数据表（示例数据，实际可通过SQLAlchemy等工具读取真实数据）
df = pd.DataFrame({
    'ID': [15, 150, 13],
    'Details': [
        'Hotel:Campsite;Message:Reservation inquiries',
        'Page:45-discount-y;PageLink:https://xx.xx.net/SS/45-discount-y/',
        None
    ]
})

# 解析Details列的键值对
def parse_details(details):
    if pd.isna(details):
        return pd.Series(dtype='object')
    kv_pairs = [pair.split(':', 1) for pair in details.split(';') if ':' in pair]
    kv_dict = {k.strip(): v.strip() for k, v in kv_pairs}
    return pd.Series(kv_dict)

# 合并拆分后的列到原表
result_df = df.join(df['Details'].apply(parse_details))

# 调整列顺序，保留原ID、Details列在前
cols = ['ID', 'Details'] + [col for col in result_df.columns if col not in ['ID', 'Details']]
result_df = result_df[cols]

print(result_df)

运行后自动识别所有出现过的键作为新列，缺失值填充为NaN（对应SQL中的NULL）。

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