如何在SQL Pivot中用TagName替换TagIndex列标题

我有两张表：

TagNames表 #

包含TagName和TagIndex字段，数据如下：

TagValues表 #

包含DateAndTime、TagIndex和Val字段，数据如下：

我已经用以下SQL实现了行转列：

WITH Tags AS (
   SELECT
      T.[TagIndex],
      T.[DateAndTime],
      T.[Val]
   FROM
      [dbo].[TagValues] T
      INNER JOIN [dbo].[TagNames] N
         ON T.TagIndex = N.TagIndex
)
SELECT *
FROM
   Tags
   PIVOT (MAX([Val]) FOR [TagIndex] IN ([0], [1], [2])) P
   ORDER BY DateAndTime
;

但得到的结果列标题是0、1、2，想把这些列标题替换为TagNames表中对应的Name1、Name2、Name3。

有两种适配不同场景的处理方式：

方式一：静态指定列别名（TagNames数据固定时用） #

直接在PIVOT后的SELECT语句中为数字列指定对应的TagName作为别名，同时保留时间字段：

WITH Tags AS (
   SELECT
      T.[TagIndex],
      T.[DateAndTime],
      T.[Val]
   FROM
      [dbo].[TagValues] T
      INNER JOIN [dbo].[TagNames] N
         ON T.TagIndex = N.TagIndex
)
SELECT 
    DateAndTime,
    [0] AS Name1,
    [1] AS Name2,
    [2] AS Name3
FROM
   Tags
   PIVOT (MAX([Val]) FOR [TagIndex] IN ([0], [1], [2])) P
ORDER BY DateAndTime;

这种方式简单直接，适合TagIndex与TagName的映射关系不会频繁变动的场景。

方式二：动态生成SQL（TagNames数据可能变动时用） #

如果TagNames表的数据可能新增或修改，静态写死列别名会增加维护成本，此时可以用动态SQL自动读取TagName并生成对应列名：

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

-- 拼接带别名的列表达式
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(TagIndex) + ' AS ' + QUOTENAME(TagName)
                      FROM [dbo].[TagNames]
                      ORDER BY TagIndex
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 生成动态PIVOT查询语句
SET @query = 'WITH Tags AS (
                  SELECT
                     T.[TagIndex],
                     T.[DateAndTime],
                     T.[Val]
                  FROM
                     [dbo].[TagValues] T
                     INNER JOIN [dbo].[TagNames] N
                        ON T.TagIndex = N.TagIndex
              )
              SELECT DateAndTime, ' + @cols + ' 
              FROM Tags
              PIVOT (MAX([Val]) FOR [TagIndex] IN (' + STUFF((SELECT ',' + QUOTENAME(TagIndex)
                                                              FROM [dbo].[TagNames]
                                                              ORDER BY TagIndex
                                                              FOR XML PATH(''), TYPE
                                                             ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ')) P
              ORDER BY DateAndTime;';

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

这种方式会自动同步TagNames表的最新数据，无需手动修改SQL语句。

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