在SQL中简单地转置列和行的方法是什么？

Question

在SQL中简单地转置列和行的方法是什么？

146

如何在SQL中简单地切换列与行？是否有任何简单的命令可以进行转置？

即将此结果转换：

        Paul  | John  | Tim  |  Eric
Red     1       5       1       3
Green   8       4       3       5
Blue    2       2       9       1

变成这样：

        Red  | Green | Blue
Paul    1       8       2
John    5       4       2
Tim     1       3       9
Eric    3       5       1

PIVOT对于这种情况似乎过于复杂。

- edezzie

1

https://dev59.com/xXPes4cB2Jgan1zndZKQ 是一个类似的问题。 - Tim Dearborn

9个回答

26

通常情况下，这要求您事先知道所有列和行的标签。如下面的查询所示，在UNPIVOT和(re)PIVOT操作中，标签都完整地列出。

MS SQL Server 2012架构设置：

create table tbl (
    color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select 
    'Red' ,1 ,5 ,1 ,3 union all select
    'Green' ,8 ,4 ,3 ,5 union all select
    'Blue' ,2 ,2 ,9 ,1;

查询 1:

select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p

结果:

| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric |   3 |     5 |    1 |
| John |   5 |     4 |    2 |
| Paul |   1 |     8 |    2 |
|  Tim |   1 |     3 |    9 |

附加说明：

给定表名，您可以使用 sys.columns 或 FOR XML 技巧使用 local-name() 确定所有列名。
您还可以使用 FOR XML 构建不同颜色的列表（或一个列的值）。
以上内容可以组合为一个动态 SQL 批处理程序来处理任何表格。

- RichardTheKiwi

12

我想指出在SQL中转置列和行的几种解决方案。

第一种是使用游标。尽管专业社区的普遍共识是要远离SQL Server Cursors，但仍有一些情况建议使用游标。无论如何，游标为我们提供了另一种选项来将行转置为列。

纵向扩展

类似于PIVOT，游标具有动态能力，可以添加更多行，以便数据集扩展以包括更多保单号码。
横向扩展

与PIVOT不同，游标在这个领域表现出色，因为它能够扩展以包括新添加的文档，而不改变脚本。
性能问题

使用CURSOR将行转置为列的主要限制是与使用游标总体上存在的缺点有关——它们会带来显著的性能成本。这是因为游标为每个FETCH NEXT操作生成一个单独的查询。

将行转置为列的另一种解决方案是使用XML。

将行转置为列的XML解决方案基本上是PIVOT的最佳版本，因为它解决了动态列的限制。

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

纵向扩展

与PIVOT和游标类似，新添加的保单可以在脚本的XML版本中检索，而无需更改原始脚本。
横向扩展

与PIVOT不同，在不更改脚本的情况下，可以显示新添加的文档。
性能问题

就IO而言，脚本的XML版本的统计信息几乎与PIVOT相似——唯一的区别是XML需要对dtTranspose表进行第二次扫描，但这次是从逻辑读取数据缓存。

您可以在本文中了解更多关于这些解决方案的信息（包括一些实际的 T-SQL 示例）：

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

- MikeS

11

根据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>
-- =============================================
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

你可以使用此命令提供的表格进行测试：

exec SQLTranspose 'yourTable', 'color'

- Paco Zarate

啊，我想要一个所有字段都是nvarchar(max)的版本。 - hamish

3

将所有 nvarchar(max) 字段中的第 6 行末尾的 sum(value) 更改为 max(value)。 - Paco Zarate

@PacoZarate 我已经使用这个工具一段时间了，谢谢！我希望输出结果可以按照透视表中第二列的顺序进行排序（第一列是透视列）。但是我观察到的是，输出结果是按照第二列的字母数字顺序排序，然后再应用行ID。行ID本质上并不重要...它只是用一个索引“标记”已经排序好的行。 - CraigD

2

在@Paco Zarate的精彩答案上补充一点，如果您想转置具有多种类型列的表格，则将此内容添加到第39行的末尾，以便仅转置int列：

and C.system_type_id = 56   --56 = type int

以下是被更改的完整查询：

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

要查找其他的"system_type_id"，请运行以下代码：

select name, system_type_id from sys.types order by name

- Jonathan Harris

2

将表中所有字段（列）的数据转换为记录（行）。

Declare @TableName  [nvarchar](128)
Declare @ExecStr    nvarchar(max)
Declare @Where      nvarchar(max)
Set @TableName = 'myTableName'
--Enter Filtering If Exists
Set @Where = ''

--Set @ExecStr = N'Select * From '+quotename(@TableName)+@Where
--Exec(@ExecStr)

Drop Table If Exists #tmp_Col2Row

Create Table #tmp_Col2Row
(Field_Name nvarchar(128) Not Null
,Field_Value nvarchar(max) Null
)

Set @ExecStr = N' Insert Into #tmp_Col2Row (Field_Name , Field_Value) '
Select @ExecStr += (Select N'Select '''+C.name+''' ,Convert(nvarchar(max),'+quotename(C.name) + ') From ' + quotename(@TableName)+@Where+Char(10)+' Union All '
         from sys.columns as C
         where (C.object_id = object_id(@TableName)) 
         for xml path(''))
Select @ExecStr = Left(@ExecStr,Len(@ExecStr)-Len(' Union All '))
--Print @ExecStr
Exec (@ExecStr)

Select * From #tmp_Col2Row
Go

- Mahmood

2

首先，我正在执行UnPivot操作并将结果存储在CTE中，然后在Pivot操作中使用CTE。

演示

with cte as
(
select 'Paul' as Name, color, Paul as Value 
    from yourTable
    union all
    select 'John' as Name, color, John as Value 
    from yourTable
    union all
    select 'Tim' as Name, color, Tim as Value 
    from yourTable
    union all
    select 'Eric' as Name, color, Eric as Value 
    from yourTable
    )
     
select Name, [Red], [Green], [Blue]
from
(
select *
from cte
) as src
pivot 
(
    max(Value)
    for color IN ([Red], [Green], [Blue])
) as Dtpivot;

- Vishwanath Dalvi

1

我喜欢分享我正在使用的代码，用于根据+bluefeet的答案对分割的文本进行转置。在这种方法中，我将其实现为MS SQL 2005中的一个过程。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      ELD.
-- Create date: May, 5 2016.
-- Description: Transpose from rows to columns the user split function.
-- =============================================
CREATE PROCEDURE TransposeSplit @InputToSplit VARCHAR(8000)
    ,@Delimeter VARCHAR(8000) = ','
AS
BEGIN
    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 = '#tempSplitedTable'

    SELECT @columnToPivot = 'col_number'

    CREATE TABLE #tempSplitedTable (
        col_number INT
        ,col_value VARCHAR(8000)
        )

    INSERT INTO #tempSplitedTable (
        col_number
        ,col_value
        )
    SELECT ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT 100
                    )
            ) AS RowNumber
        ,item
    FROM [DB].[ESCHEME].[fnSplit](@InputToSplit, @Delimeter)

    SELECT @colsUnpivot = STUFF((
                SELECT ',' + quotename(C.NAME)
                FROM [tempdb].sys.columns AS C
                WHERE C.object_id = object_id('tempdb..' + @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
        (
            MAX(value)
            for ' + @columnToPivot + ' in (' + @colsPivot + ')
        ) piv
        order by rowid'

    EXEC (@query)

    DROP TABLE #tempSplitedTable
END
GO

我正在将关于如何在没有order by的情况下对行进行排序的信息（SQLAuthority.com）和MSDN上的split函数混合在一起（social.msdn.microsoft.com）。

当您执行该过程时，

DECLARE @RC int
DECLARE @InputToSplit varchar(MAX)
DECLARE @Delimeter varchar(1)
    
set @InputToSplit = 'hello|beautiful|world'
set @Delimeter = '|'
    
EXECUTE @RC = [TransposeSplit] 
    @InputToSplit
    ,@Delimeter
GO

你获得了下一个结果

名称	行号	1	2	3
列值	1	你好	美丽	世界

- Elidio Marquina

0

我能够使用Paco Zarate的解决方案，并且它运行得非常好。我确实需要添加一行代码（“SET ANSI_WARNINGS ON”），但这可能是我使用或调用方式独特的问题。我在使用过程中遇到了一个问题，希望有人能帮助我：

该解决方案仅适用于实际的SQL表。我尝试将其与临时表和内存（已声明）表一起使用，但无法使用。因此，在我的调用代码中，我在SQL数据库上创建一个表，然后调用SQLTranspose。再次，它运行得非常好。这正是我想要的。以下是我的问题：

为了使整体解决方案真正动态化，我需要在“即时”创建并发送给SQLTranspose的准备信息的临时存储表中创建该表，然后在调用SQLTranspose后删除该表。表格删除会对我的最终实现计划造成问题。代码需要从最终用户应用程序（Microsoft Access表单/菜单上的按钮）运行。当我使用此SQL进程（创建SQL表，调用SQLTranspose，删除SQL表）时，最终用户应用程序会出现错误，因为使用的SQL帐户没有删除表的权限。

因此，我认为有几种可能的解决方案：

找到一种让SQLTranspose能够使用临时表或已声明的表变量的方法。
找出另一种行列转置的方法，它不需要实际的SQL表。
找出一个合适的方法，允许我的终端用户使用由我Access应用程序编码的单个共享SQL账户删除表。似乎权限是无法授予的dbo类型特权。

我知道其中一些可能需要另一个独立的主题和问题。然而，由于有可能解决方案只是一种不同的行列转置方法，我将在此线程中发布我的第一篇帖子。

编辑：我也像Paco建议的那样，在倒数第6行中用max(value)替换了sum（value）。

编辑：

我找到了适合自己的解决方案。我不知道它是否是最好的答案。

我有一个只读用户帐户，用于执行存储过程并从数据库生成报表输出。由于我创建的SQLTranspose函数仅适用于“合法”表（不是已声明的表，也不是临时表），因此我不得不想出一种方法让只读用户帐户创建（然后稍后删除）表格。

我认为对于我的目的来说，允许用户帐户创建表格是可以的。但用户仍然无法删除表格。我的解决方案是创建一个授权用户帐户的模式。然后每当我创建、使用或删除那个表格时，都要指定该模式。

我首先从“sa”或“sysadmin”帐户发出了以下命令：

CREATE SCHEMA ro AUTHORIZATION

每当我提到我的“tmpoutput”表格时，我会像这个例子一样指定它：

drop table ro.tmpoutput

- CraigD

我找到了适合我的解决方案。我不知道它是否是最好的答案。 - CraigD

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

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

- Taryn · Accepted Answer

有几种方法可以转换这些数据。在您的原始帖子中，您表示这种情况下似乎使用 PIVOT 太过复杂，但是在 SQL Server 中，可以很容易地使用 UNPIVOT 和 PIVOT 函数进行应用。

然而，如果您无法访问这些函数，则可以使用 UNION ALL 进行复制到 UNPIVOT，然后再使用聚合函数和 CASE 语句进行 PIVOT： 创建表：

CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);

INSERT INTO yourTable
    ([color], [Paul], [John], [Tim], [Eric])
VALUES
    ('Red', 1, 5, 1, 3),
    ('Green', 8, 4, 3, 5),
    ('Blue', 2, 2, 9, 1);

Union All、聚合函数和CASE版本：

select name,
  sum(case when color = 'Red' then value else 0 end) Red,
  sum(case when color = 'Green' then value else 0 end) Green,
  sum(case when color = 'Blue' then value else 0 end) Blue
from
(
  select color, Paul value, 'Paul' name
  from yourTable
  union all
  select color, John value, 'John' name
  from yourTable
  union all
  select color, Tim value, 'Tim' name
  from yourTable
  union all
  select color, Eric value, 'Eric' name
  from yourTable
) src
group by name

请查看SQL Fiddle with Demo。

UNION ALL通过将列Paul, John, Tim, Eric转换为单独的行来执行数据的UNPIVOT。然后，您可以使用case语句应用聚合函数sum()来获取每个color的新列。

静态版本的Unpivot和Pivot：

在SQL Server中，UNPIVOT和PIVOT函数都使此转换更加容易。如果您知道要转换的所有值，则可以将它们硬编码到静态版本中以获得结果：

select name, [Red], [Green], [Blue]
from
(
  select color, name, value
  from yourtable
  unpivot
  (
    value for name in (Paul, John, Tim, Eric)
  ) unpiv
) src
pivot
(
  sum(value)
  for color in ([Red], [Green], [Blue])
) piv

请参见 SQL Fiddle with Demo。

UNPIVOT 的内部查询与 UNION ALL 执行相同的功能。它将列的列表转换为行，然后 PIVOT 将执行最终的列转换。

动态旋转版本：

如果您有未知数量的列（例如您的示例中的 Paul, John, Tim, Eric），然后是要转换的未知数量的颜色，则可以使用动态 SQL 生成要传递给 UNPIVOT 和 PIVOT 的列表：

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

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

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(color)
                    from yourtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select name, '+@colsPivot+'
      from
      (
        select color, name, value
        from yourtable
        unpivot
        (
          value for name in ('+@colsUnpivot+')
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for color in ('+@colsPivot+')
      ) piv'

exec(@query)

请参见 SQL Fiddle with Demo。

动态版本查询了yourtable和sys.columns表，以生成要UNPIVOT和PIVOT的项目列表。然后将其添加到要执行的查询字符串中。动态版本的优点是，如果您有一个不断变化的colors和/或names列表，则会在运行时生成该列表。

所有三个查询都会产生相同的结果：

| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric |   3 |     5 |    1 |
| John |   5 |     4 |    2 |
| Paul |   1 |     8 |    2 |
|  Tim |   1 |     3 |    9 |