SQL Server中如何通过查询为每个单词生成动态列？

嗨，这个需求完全可以实现！针对SQL Server，我们可以通过字符串拆分+动态透视的方式，把列中的每个单词转换成动态列。考虑到你有150万条记录的规模，我会把实现方案和性能优化点都讲清楚～

要实现这个需求，主要分三步走：

• 第一步：把存储值的列按空格拆分，得到每条原始记录对应的单个单词行（同时保留单词的顺序）
• 第二步：提取所有唯一的单词，作为要生成的动态列名
• 第三步：用动态SQL结合PIVOT运算符，把拆分后的行数据转换成列数据

先假设你的表名为YourTable，存储单词的列叫ValueColumn，还有一个主键列ID用来标识每条原始记录（如果没有主键，建议加一个唯一标识列，避免数据混乱）。

1. 示例数据准备 #

先创建一个测试表方便理解：

CREATE TABLE YourTable (
    ID INT PRIMARY KEY,
    ValueColumn NVARCHAR(MAX)
);

INSERT INTO YourTable VALUES
(1, 'Apple Banana Cherry'),
(2, 'Banana Cherry Date'),
(3, 'Apple Date');

2. 拆分字符串并保留顺序 #

SQL Server 2022及以上版本的STRING_SPLIT支持ordinal参数，可以直接保留单词在原字符串中的顺序；如果是2016-2019版本，需要用自定义方法来标记顺序（比如递归CTE）。这里以2022+版本为例：

SELECT 
    ID,
    ValueColumn,
    value AS Word,
    ordinal AS WordPosition -- 标记单词是第几个
FROM YourTable
CROSS APPLY STRING_SPLIT(ValueColumn, ' ', 1); -- 最后一个1表示返回ordinal序号

3. 生成动态透视SQL #

我们需要先提取所有唯一的单词作为列名，再动态构建透视语句：

DECLARE @Columns NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 提取所有唯一单词，拼接成列名（SQL Server 2017+用STRING_AGG）
SELECT @Columns = STRING_AGG(QUOTENAME(Word), ', ')
FROM (
    SELECT DISTINCT value AS Word
    FROM YourTable
    CROSS APPLY STRING_SPLIT(ValueColumn, ' ')
) AS UniqueWords;

-- 针对2016及以下版本，用STUFF+FOR XML PATH拼接列名
-- SELECT @Columns = STUFF((
--     SELECT ', ' + QUOTENAME(value)
--     FROM (SELECT DISTINCT value AS Word FROM YourTable CROSS APPLY STRING_SPLIT(ValueColumn, ' ')) AS UniqueWords
--     FOR XML PATH(''), TYPE
-- ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 构建动态透视SQL
SET @DynamicSQL = N'
SELECT ID, ' + @Columns + N'
FROM (
    SELECT 
        ID,
        value AS Word,
        ordinal AS WordPosition
    FROM YourTable
    CROSS APPLY STRING_SPLIT(ValueColumn, '' '', 1)
) AS SplitData
PIVOT (
    MAX(Word) -- 用MAX是因为每个位置对应唯一单词，聚合不影响结果
    FOR WordPosition IN (' + @Columns + N')
) AS PivotResult;';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

执行后就能得到每条记录对应单词的动态列，空值表示该记录没有这个单词。

因为数据量很大，直接操作原表可能会很慢，这些优化点能帮你提升效率：

• 索引优化：确保YourTable的ID列是主键（聚集索引）；如果ValueColumn经常需要拆分，可以创建包含ValueColumn的非聚集索引，减少查询时的IO开销。
• 临时表缓存拆分结果：先把拆分后的结果存入临时表，再基于临时表做透视，避免重复拆分原表的大字段：

  -- 把拆分结果存入临时表
  SELECT 
      ID,
      value AS Word,
      ordinal AS WordPosition
  INTO #SplitData
  FROM YourTable
  CROSS APPLY STRING_SPLIT(ValueColumn, ' ', 1);
  
  -- 给临时表加索引，加速后续透视操作
  CREATE CLUSTERED INDEX IX_SplitData_ID ON #SplitData(ID);
  CREATE NONCLUSTERED INDEX IX_SplitData_Word ON #SplitData(Word);
  
  -- 后续的动态SQL基于#SplitData来构建即可
  

• 评估列数量合理性：如果单词数量特别多（比如上千个），生成的动态列会非常多，不仅查询慢，结果也难以处理。这种情况建议考虑用JSON或XML格式返回拆分后的单词，更适合大数据量场景。

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