如何高效将网格表转为XYZ绘图视图？替代Union All批量列转行

问题场景 #

我有一张Mesh Table（网格表），需转换为XYZ Plot View（XYZ绘图视图）以导入应用生成3D数据图表。该表包含Seq（X）列、Date（Y）列，以及约520个数据点列（Z列，命名为D_1至D_520）。目前通过手动编写UNION ALL语句创建视图，但要覆盖到D_520非常耗时，希望找到更简便高效的实现方式。当前手动实现的代码示例如下：

Select   
    seq as x,
    t_stamp as y,
    D_1 as z
From
    dbo.Table_3D_data
Union All
Select  
    seq as x,
    t_stamp as y,
    D_2 as z
From
    dbo.Table_3D_data
Union All   -- 此处省略D_3至D_499的重复语句
Select   
    seq as x,
    t_stamp as y,
    D_520 as z
From
    dbo.Table_3D_data 
Order By 
    seq

高效解决方案 #

方法1：使用UNPIVOT运算符 #

SQL Server提供的UNPIVOT运算符可以直接将列转换为行，无需手动编写大量UNION ALL，且性能更优（仅需扫描一次表）。示例代码：

SELECT 
    seq AS x,
    t_stamp AS y,
    z
FROM 
    dbo.Table_3D_data
UNPIVOT (
    z FOR D_Columns IN (
        D_1, D_2, D_3,
        -- 依次列出D_4至D_499
        D_520
    )
) AS unpvt
ORDER BY 
    seq;

方法2：动态SQL自动生成列列表 #

如果不想手动列出520个列名，可以通过查询系统视图自动获取所有D_开头的列，动态生成UNPIVOT语句：

适用于SQL Server 2017及以上（支持STRING_AGG）

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- 获取所有D_前缀的列名并拼接
SELECT @cols = STRING_AGG(QUOTENAME(column_name), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Table_3D_data' 
  AND column_name LIKE 'D[_]%'
ORDER BY column_name;

-- 生成完整查询语句
SET @query = N'
SELECT 
    seq AS x,
    t_stamp AS y,
    z
FROM 
    dbo.Table_3D_data
UNPIVOT (
    z FOR D_Columns IN (' + @cols + N')
) AS unpvt
ORDER BY 
    seq;';

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

适用于SQL Server 2016及以下（用FOR XML PATH拼接）

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- 拼接列名
SELECT @cols = STUFF((
    SELECT ', ' + QUOTENAME(column_name)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'Table_3D_data' 
      AND column_name LIKE 'D[_]%'
    ORDER BY column_name
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 生成并执行查询
SET @query = N'
SELECT 
    seq AS x,
    t_stamp AS y,
    z
FROM 
    dbo.Table_3D_data
UNPIVOT (
    z FOR D_Columns IN (' + @cols + N')
) AS unpvt
ORDER BY 
    seq;';

EXEC sp_executesql @query;

注意事项 #

• UNPIVOT相比手动UNION ALL，仅需扫描一次源表，执行效率大幅提升。
• 动态SQL方案可自动适配列的增减，后续新增D_列无需修改代码，重新执行即可生成最新的视图数据。

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