SQL 将列转换为行

发布于 2024-07-14 00:04:04 字数 263 浏览 4 评论 0原文

我的表有以下列：

A  |  B  |  C  |  D  |  E  |  F

我想按如下方式显示：

MyColumn  |  MyColumn2
  A       |    B
  C       |    D
  E       |    F

如您所见，我想将这些列与自定义列名称成对显示。这些对是 A 列和 B 列、C 列和 D 列以及 C 列和 D 列。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

壹場煙雨 2024-07-21 00:04:04

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

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

回复收藏 0 原文

旧情别恋 2024-07-21 00:04:04

如果我理解正确，您可以使用联合来做到这一点：

SELECT A as MyColumn, B as MyColumn2
UNION
SELECT C as MyColumn, D as MyColumn2
UNION
SELECT E as MyColumn, F as MyColumn2

如果您的数据类型与匹配列不同，那么您需要首先转换数据类型，所以类似：

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

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

回复收藏 0 原文

小红帽 2024-07-21 00:04:04

这不会扩展到十亿列，但是......

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

这个查询将删除重复项。假设表中有一条记录

你好| 世界 | 你好 | 世界 | 你好 | 世界

My select 只会返回

你好| 世界

三宝将回归全部三...

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