SQL Server：将行转换为列

Question

SQL Server：将行转换为列

sqlt-sqlsql-server-2005

36

除了编写光标读取每行并将其填充到列中的代码之外，如果我需要将每行转置为列，还有其他替代方法吗？

TimeSeconds TagID Value
1378700244  A1    3.75
1378700245  A1    30
1378700304  A1    1.2
1378700305  A2    56
1378700344  A2    11
1378700345  A3    0.53
1378700364  A1    4
1378700365  A1    14.5
1378700384  A1    144
1378700384  A4    10

列数不是固定的。

输出：我只是将 n/a 分配为该交叉点中没有数据的占位符。

TimeSec     A1    A2    A3    A4
1378700244  3.75  n/a   n/a   n/a
1378700245  30    n/a   n/a   n/a
1378700304  1.2   n/a   n/a   n/a
1378700305  n/a   56    n/a   n/a
1378700344  n/a   11    n/a   n/a
1378700345  n/a   n/a   0.53  n/a
1378700364  n/a   n/a   n/a   4
1378700365  14.5  n/a   n/a   n/a
1378700384  144   n/a   n/a   10

希望你能与我分享一些贴士。谢谢。

- user3015739

5个回答

15

SQL Server有一个PIVOT命令，这可能是你正在寻找的。

select * from Tag
pivot (MAX(Value) for TagID in ([A1],[A2],[A3],[A4])) as TagTime;

如果列不是常量，那么您需要将其与一些动态SQL结合使用。

DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);

select @columns = substring((Select DISTINCT ',' + QUOTENAME(TagID) FROM Tag FOR XML PATH ('')),2, 1000);

SELECT @sql =

'SELECT *
FROM TAG
PIVOT 
(
  MAX(Value) 
  FOR TagID IN( ' + @columns + ' )) as TagTime;';

 execute(@sql);

- sarme

1

哇...你的动态SQL太棒了。我得查一下QUOTENAME和XML PATH函数是什么。感谢你提供的示例。 - user3015739

1

谢谢。我在列名中使用空格时被烦了几次，直到我找到了QUOTENAME。 - sarme

1

是的，QUOTENAME是一个很好的函数，我只是不明白为什么有些应用程序使用像TIME，VALUE这样的列名。使用QUOTENAME可以避免这些问题。 - user3015739

1

我成功地使用了你的脚本来执行我的SQL。速度很快，但不幸的是，我在使用Business Object绘制图表时遇到了问题。最初，我的数据集返回了超过300k行和15个系列。我认为，将行转置为列将有助于创建我想要的图表。然而，在转置并在Business Object中绘制它之后，我无法获得描述这些标签的图例。真是一个挑战... - user3015739

10

在这种情况下，可能适合使用XML选项。

将行转换为列的XML选项基本上是PIVOT的最佳版本，因为它解决了动态列限制的问题。

脚本的XML版本通过使用XML Path、动态T-SQL和一些内置函数（例如STUFF、QUOTENAME）组合来解决此限制。

垂直扩展

与PIVOT和Cursor类似，在XML版本的脚本中添加新策略时，无需更改原始脚本即可检索到。

水平扩展

与PIVOT不同，可以在不修改脚本的情况下显示新增的文档。

性能分解

就IO而言，脚本的XML版本的统计数据几乎与PIVOT相似，唯一的区别在于XML有dtTranspose表的第二个扫描，但这次是从逻辑读取-数据缓存。

您可以在以下文章中找到更多关于这些解决方案的信息（包括一些实际的T-SQL示例）： https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

- FloresP

2

基于bluefeet提供的solution，这里有一个使用动态SQL生成转置表格的存储过程。它要求除了转置列（在结果表中将成为标题的列）外，所有字段都是数字类型：

/****** Object:  StoredProcedure [dbo].[SQLTranspose]    Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for     transposing.
-- Parameters: @TableName - Table to transpose
--             @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
--        table and FIeldToTranspose should be written using single quotes
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose] 
  -- Add the parameters for the stored procedure here
  @TableName NVarchar(MAX) = '', 
  @FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  DECLARE @colsUnpivot AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @queryPivot  AS NVARCHAR(MAX),
  @colsPivot as  NVARCHAR(MAX),
  @columnToPivot as NVARCHAR(MAX),
  @tableToPivot as NVARCHAR(MAX), 
  @colsResult as xml

  select @tableToPivot = @TableName;
  select @columnToPivot = @FieldNameTranspose


  select @colsUnpivot = stuff((select ','+quotename(C.name)
       from sys.columns as C
       where C.object_id = object_id(@tableToPivot) and
             C.name <> @columnToPivot 
       for xml path('')), 1, 1, '')

  set @queryPivot = 'SELECT @colsResult = (SELECT  '','' 
                    + quotename('+@columnToPivot+')
                  from '+@tableToPivot+' t
                  where '+@columnToPivot+' <> ''''
          FOR XML PATH(''''), TYPE)'

  exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out

  select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')

  set @query 
    = 'select name, rowid, '+@colsPivot+'
        from
        (
          select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
          from '+@tableToPivot+'
          unpivot
          (
            value for name in ('+@colsUnpivot+')
          ) unpiv
        ) src
        pivot
        (
          sum(value)
          for '+@columnToPivot+' in ('+@colsPivot+')
        ) piv
        order by rowid'
  exec(@query)
END

- Paco Zarate

1

我有一个稍微不同的要求，需要将某些列有选择地转换为行。

表格有以下列：

create table tbl (ID, PreviousX, PreviousY, CurrentX, CurrentY)

我需要为Previous和Current创建列，并且为X和Y创建行。在静态表上生成的笛卡尔积效果很好，例如：

select 
    ID,
    max(case when metric='X' then PreviousX
        case when metric='Y' then PreviousY end) as Previous,
    max(case when metric='X' then CurrentX
        case when metric='Y' then CurrentY end) as Current
from tbl inner join
    /* Cartesian product - transpose by repeating row and 
    picking appropriate metric column for period */
    ( VALUES (1, 'X'), (2, 'Y')) AS x (sort, metric) ON 1=1
group by ID
order by ID, sort

- Chris

1

然而，OP要求“将行转置为列”。这是一个很好的答案，但不是针对这个问题的。 - Reversed Engineer

1

绝对正确，我是顶部答案的一位点赞者。只是想为有类似需求且需要更多控制的人提供便利。 - Chris

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- peterm · Accepted Answer

如果已经提前知道tagID的值，一种方法是使用条件聚合

SELECT TimeSeconds,
       COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1,
       COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2,
       COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3,
       COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4
  FROM table1
 GROUP BY TimeSeconds

或者，如果您可以接受使用NULL值而不是'n/a'

SELECT TimeSeconds,
       MAX(CASE WHEN TagID = 'A1' THEN Value END) A1,
       MAX(CASE WHEN TagID = 'A2' THEN Value END) A2,
       MAX(CASE WHEN TagID = 'A3' THEN Value END) A3,
       MAX(CASE WHEN TagID = 'A4' THEN Value END) A4
  FROM table1
 GROUP BY TimeSeconds

或者使用 PIVOT

SELECT TimeSeconds, A1, A2, A3, A4
  FROM
(
  SELECT TimeSeconds, TagID, Value
    FROM table1
) s
PIVOT
(
  MAX(Value) FOR TagID IN (A1, A2, A3, A4)
) p

输出结果（包括NULL）：

TimeSeconds A1      A2     A3    A4
----------- ------- ------ ----- -----
1378700244  3.75    NULL   NULL  NULL
1378700245  30.00   NULL   NULL  NULL
1378700304  1.20    NULL   NULL  NULL
1378700305  NULL    56.00  NULL  NULL
1378700344  NULL    11.00  NULL  NULL
1378700345  NULL    NULL   0.53  NULL
1378700364  4.00    NULL   NULL  NULL
1378700365  14.50   NULL   NULL  NULL
1378700384  144.00  NULL   NULL  10.00

如果必须动态地确定TagID值，则使用动态SQL。

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID)
            FROM Table1
            ORDER BY 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @sql = 'SELECT TimeSeconds, ' + @cols + '
              FROM
            (
              SELECT TimeSeconds, TagID, Value
                FROM table1
            ) s
            PIVOT
            (
              MAX(Value) FOR TagID IN (' + @cols + ')
            ) p'

EXECUTE(@sql)