在mysql表中如何变换列和行_在SQL中转换列和行的简单方法？

有几种方法可以转换这些数据。在你最初的帖子中，你说PIVOT对于这个场景来说似乎太复杂了，但是可以很容易地使用UNPIVOT和PIVOTSQL Server中的函数。

但是，如果您无法访问这些函数，则可以使用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，聚合和案例版本：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) Bluefrom(

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) srcgroup by name

这个UNION ALL执行UNPIVOT通过转换列来对数据进行转换。Paul, John, Tim, Eric分成几排。然后应用聚合函数。sum()带着case语句获取每个列的新列。color.

枢轴和枢轴静态版本：

双双UNPIVOT和PIVOTSQL Server中的函数使这种转换更加容易。如果您知道要转换的所有值，可以将它们硬编码到静态版本中以获得结果：select name, [Red], [Green], [Blue]from(

select color, name, value  from yourtable  unpivot

(

value for name in (Paul, John, Tim, Eric)

) unpiv) srcpivot(

sum(value)

for color in ([Red], [Green], [Blue])) piv

对象的内部查询。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)

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