SQL 将列转换为行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/560621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Convert column to row
提问by Martijn
My table has the following columns:
我的表有以下列：
A | B | C | D | E | F
I want to displays this as follow:
我想显示如下：
MyColumn | MyColumn2
A | B
C | D
E | F
As you can see i want to display the columns as pairs with a custom column name. The pairs are Column A and B, column C and D and column C and D.
如您所见，我想将列显示为具有自定义列名称的对。这些对是 A 列和 B 列、C 列和 D 列以及 C 列和 D 列。
采纳答案by Sam Saffron
select A as [Col 1], B as [Col 2] from table
union all
select C,D from table
union all
select E,F from table
回答by Jason Punyon
This won't scale to a billion columns but...
这不会扩展到十亿列，但是......
Select A as MyColumn, B as MyColumn2 from Table
UNION
Select C as MyColumn, D as MyColumn2 from Table
UNION
Select E as MyColumn, F as MyColumn2 from Table
This query will remove the duplicates though. So say there's one record in the table
不过，此查询将删除重复项。所以说表中有一条记录
Hello | World | Hello | World | Hello | World
你好 | 世界| 你好 | 世界| 你好 | 世界
My select will only return
我的选择只会返回
Hello | World
你好 | 世界
Sambo's will return all three...
三宝将返回所有三个......
回答by tddmonkey
If I'm understanding you correctly you can do this with a union:
如果我正确理解你，你可以通过工会来做到这一点：
SELECT A as MyColumn, B as MyColumn2
UNION
SELECT C as MyColumn, D as MyColumn2
UNION
SELECT E as MyColumn, F as MyColumn2
If your datatypes aren't the same for the matching columns then you'll need to convert the datatype first, so something like:
如果匹配列的数据类型不同，则需要先转换数据类型，例如：
SELECT CONVERT(VARCHAR(10), A) as MyColumn, CONVERT(VARCHAR(10), B) as MyColumn2
UNION
SELECT CONVERT(VARCHAR(10), C) as MyColumn, CONVERT(VARCHAR(10), D) as MyColumn2
UNION
SELECT CONVERT(VARCHAR(10), E) as MyColumn, CONVERT(VARCHAR(10), F) as MyColumn2
回答by tddmonkey
In Sql Server 2005 Unpivot operator can solve your problem. Unpivot converts columns to rows in sql server 2005.
在 Sql Server 2005 中 Unpivot 运算符可以解决您的问题。 Unpivot 在 sql server 2005 中将列转换为行。
Hope it helps
希望能帮助到你
