在 SQL 中转换列和行的简单方法？

如何在 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似乎太复杂了。

749785 次浏览
小开

这通常要求您事先知道所有的列和行标签。正如您在下面的查询中可以看到的，在 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 |

附加说明:

  1. 给定一个表名，就可以使用 Local-name ()从 Sys.column或 FORXML 技巧中确定所有列的名称。
  2. 还可以使用 FORXML 构建不同颜色(或一列的值)的列表。
  3. 以上内容可以组合成一个动态 sql 批处理来处理任何表。
小开
最佳答案

有几种方法可以转换此数据。在您最初的文章中，您说过 PIVOT对于这个场景来说似乎太复杂了，但是它可以很容易地使用 SQLServer 中的两个 ABC1和 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);

全联盟，总体和案例版本:

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 小提琴

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

Unpivot 和 Pivot 静态版本:

SQL 服务器中的 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 小提琴

使用 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 小提琴

动态版本同时查询 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 |
小开

基于 蓝脚的这个 解决方案，这里有一个存储过程，它使用动态 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'
小开

我喜欢分享我使用的代码转换一个分裂的文本基于 + 蓝脚的答案。在这种方法中，我是作为一个程序在 MSSQL 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

我将这个解决方案与关于如何通过(Sqlauthority.com)和 MSDN (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

你会得到下一个结果

  name       rowid  1      2          3
col_value  1      hello  beautiful  world
小开

我想指出一些在 SQL 中转换列和行的解决方案。

第一个是-使用 CURSOR。尽管专业团体的普遍共识是远离 SQLServer 游标，但仍有一些情况建议使用游标。无论如何，Cursor 为我们提供了另一个将行转换为列的选项。

  • 垂直扩张

    与 PIVOT 类似，当数据集扩展到包含更多策略编号时，游标具有附加更多行的动态能力。

  • 横向扩张

    与 PIVOT 不同，光标在这方面表现出色，因为它能够扩展到包含新添加的文档，而不需要更改脚本。

  • 性能分析

    使用 CURSOR 将行转换为列的主要限制是一个缺点，这个缺点通常与使用游标有关——它们的性能成本很高。这是因为游标为每个 FETCH NEXT 操作生成一个单独的查询。

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

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

该脚本的 XML 版本通过结合使用 XML Path、动态 T-SQL 和一些内置函数(即 STUFF、 QUOTENAME)来解决这个限制。

  • 垂直扩张

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

  • 横向扩张

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

  • 性能分析

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

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

小开

首先执行 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;
小开

再加上@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
小开

这样就可以将表中的所有字段(列)中的数据转换为记录(行)。

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
小开

我能够使用帕科萨拉特的解决方案，它的工程美丽。我确实需要添加一行代码(“ SET ANSI _ WARNING ON”) ，但这可能是我使用或调用它的方式所特有的。我的用法有问题，我希望有人能帮我解决:

该解决方案仅适用于实际的 SQL 表。我尝试使用一个临时表和一个内存中(声明的)表，但它无法与这些表一起工作。因此，在调用代码中，我在 SQL 数据库上创建一个表，然后调用 SQLTransose。再说一次，效果很好。这正是我想要的。我的问题是:

为了使整个解决方案真正具有动态性，我需要创建一个表，在该表中临时存储准备好的信息，并“动态”发送给 SQLTransose，然后在调用 SQLTransose 时删除该表。表的删除给我的最终实现计划带来了问题。代码需要从终端用户应用程序(MicrosoftAccess 表单/菜单上的按钮)运行。当我使用这个 SQL 进程(创建一个 SQL 表，调用 SQLTransose，删除 SQL 表)时，最终用户应用程序会遇到一个错误，因为使用的 SQL 帐户没有删除表的权限。

所以我认为有几种可能的解决办法:

  1. 找到一种方法，使 SQLTransose 能够使用临时表或声明的表变量。

  2. 为不需要实际 SQL 表的行和列的转换找到另一种方法。

  3. 找出一种适当的方法，允许我的最终用户使用的 SQL 帐户删除一个表。它是编码到我的 Access 应用程序中的一个共享 SQL 帐户。似乎权限是一种不能授予的 dbo 类型的特权。

我认识到，其中一些可能需要另一个，单独的线索和问题。然而，由于有一种可能性，一个解决方案可能仅仅是一种不同的方法来做行和列的转换，我将在这个线程中发表我的第一篇文章。

编辑: 正如 Paco 建议的那样，我还在结尾的第6行用 max (value)替换了 sum (value)。

编辑:

我想出了一个对我有用的方法，我不知道这是否是 最好的的答案。

我有一个只读用户帐户，用于执行字符串过程，从而从数据库生成报告输出。由于我创建的 SQLTransose 函数只能用于“合法”表(不是声明的表，也不是临时表) ，所以我必须想办法让一个只读用户帐户创建(然后删除)一个表。

根据我的理由，允许用户帐户创建表是可以的。但是用户仍然无法删除该表。我的解决方案是创建一个授权用户帐户的模式。然后，每当我创建、使用或删除该表时，使用指定的模式引用它。

我首先从‘ sa’或‘ sysadmin’帐户发出这个命令: 创建模式代码授权

每当我引用“ tmpoutput”表时，我都会像下面这样指定它:

删除表 ro.tmpoutput


提交答案