如何基于X、Y位置动态SQL Pivot表？已提取列名但无法获取数据

首先我先假设你的源表结构大概是这样的（如果和实际不符，你可以调整字段名）：

CREATE TABLE YourTable (
    FileName VARCHAR(100),
    AThing VARCHAR(50),
    X INT, -- 列位置标识
    Y INT, -- 行位置标识
    CellValue VARCHAR(255) -- 对应X/Y位置的内容值
);

接下来咱们一步步搞定动态透视的实现，毕竟你已经搞定了列名提取，差的就是把数据和列名结合起来的关键步骤：

1. 完善透视列名的构造（适配不同SQL Server版本） #

你已经在提取列名了，这里给你补全两种版本的写法，确保兼容：

DECLARE @PivotColumns NVARCHAR(MAX);

-- SQL Server 2016及以上版本用STRING_AGG，简洁高效
SELECT @PivotColumns = STRING_AGG(QUOTENAME(X), ', ') WITHIN GROUP (ORDER BY X)
FROM (SELECT DISTINCT X FROM YourTable) AS DistinctX;

-- 如果是2016之前的版本，用FOR XML PATH的老写法
/*
SELECT @PivotColumns = STUFF(
    (SELECT ', ' + QUOTENAME(X)
     FROM (SELECT DISTINCT X FROM YourTable) AS DistinctX
     ORDER BY X
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
);
*/

2. 构造完整的动态透视SQL语句 #

这里分两种常见场景，你根据自己的需求选：

场景1：按Y（行位置）分组，透视X（列位置）为列 #

忽略AThing和FileName，只保留行位置和对应各列的值：

DECLARE @DynamicSQL NVARCHAR(MAX);
SET @DynamicSQL = N'
SELECT Y, ' + @PivotColumns + '
FROM (
    -- 只筛选需要的字段，直接排除AThing和FileName
    SELECT Y, X, CellValue
    FROM YourTable
) AS SourceData
PIVOT (
    -- 这里根据值类型选聚合函数：数值用SUM/AVG，字符串用MAX
    MAX(CellValue)
    FOR X IN (' + @PivotColumns + ')
) AS PivotResult
ORDER BY Y; -- 按行位置排序，保证行顺序和实际一致
';

场景2：按FileName分组，每个文件生成独立的透视表 #

如果每个文件的X/Y结构是独立的，需要保留文件标识：

DECLARE @DynamicSQL NVARCHAR(MAX);
SET @DynamicSQL = N'
SELECT FileName, Y, ' + @PivotColumns + '
FROM (
    SELECT FileName, Y, X, CellValue
    FROM YourTable
) AS SourceData
PIVOT (
    MAX(CellValue)
    FOR X IN (' + @PivotColumns + ')
) AS PivotResult
ORDER BY FileName, Y;
';

3. 执行动态SQL #

最后一步执行构造好的语句就能得到透视结果了：

EXEC sp_executesql @DynamicSQL;

常见踩坑点排查 #

• 如果你看不到对应数据，先检查子查询里的字段是否正确，有没有漏传CellValue这类关键值
• 聚合函数要匹配值类型：如果是数值型数据，用SUM比MAX更合理；如果是字符串，MAX可以保证取到唯一值（同一X/Y位置如果有重复值的话）
• 列顺序不对？检查@PivotColumns生成时有没有加ORDER BY X，确保列按X位置从小到大排列

内容的提问来源于stack exchange，提问作者Jamie Stuart Robin Parsons