如何在 SQL Server select 语句中将单列拆分为多列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29652642/
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
How to split a single column into multiple columns in SQL Server select statement?
提问by Mian Asbat Ahmad
I am new to SQL Server . I have a single long column with names starting from a, b, c and d. I want to show them in separate columns of NameA, NameB, NameC and NameD. I tried union but it shows in one column. I tried case but I dont know how to use it. Please help.
我是 SQL Server 的新手。我有一个长列，名称从 a、b、c 和 d 开始。我想在 NameA、NameB、NameC 和 NameD 的单独列中显示它们。我试过 union 但它显示在一列中。我试过 case 但我不知道如何使用它。请帮忙。
Existing column
现有列
names
A1
B1
A2
C1
A3
A4
A_names| B_names | C_names
A1 | B1 | C1
A2
A3
A4
回答by A ツ
just for fun and curious why you want that:
只是为了好玩和好奇你为什么想要那个：
select *
from
( select idx = left(names,1)
, names
, rn = row_number() over (partition by left(names,1) order by names)
from
( values ('A1'),('B1'),('A2'),('C1'),('A3'),('A4'),('B2'))
v(names)
) dat
pivot
( max(names)
for idx in ([A],[B],[C],[D])
) p
回答by wvdz
I don't think this is something that should be solved in SQL. It's a representational thing that should probably be done in the application.
我不认为这是应该在 SQL 中解决的问题。这是应该在应用程序中完成的具有代表性的事情。
However, if you insist to use SQL for this, this is how you could do it. The main problem with this query is that the ROW_NUMBER function will be quite bad for performance.
但是，如果您坚持为此使用 SQL，这就是您可以做到的。此查询的主要问题是 ROW_NUMBER 函数对性能非常不利。
WITH nameA
(
SELECT name, ROW_NUMBER() OVER(ORDER BY name) AS rn
FROM t1
WHERE name LIKE 'a%'
), nameB AS
(
SELECT name, ROW_NUMBER() OVER(ORDER BY name) AS rn
FROM t1
WHERE name LIKE 'b%'
)
SELECT name FROM nameA
FULL OUTER JOIN nameB
ON nameA.rn = nameB.rn
ORDER BY nameA.rn,nameB.rn;
回答by Dan Field
This isn't really the way relational databases work. When you have data in the same row, it's supposed to be related in some way - a common ID, at the least. What is it that would connect the person whose name happens to begin with A to the person whose name happens to begin with B? Why would you ever want the RDBMS to make such an arbitrary connection?
这并不是关系数据库的真正工作方式。当您在同一行中有数据时，它应该以某种方式相关 - 至少是一个公共 ID。是什么将名字恰好以 A 开头的人与名字恰好以 B 开头的人联系起来？为什么您会希望 RDBMS 进行这样的任意连接？
If you have a requirement to display users in such a way, you'd just want to write several queries and have your presentation layer deal with laying them out properly, e.g.
如果您需要以这种方式显示用户，您只需编写几个查询并让您的表示层处理正确布局它们，例如
SELECT name FROM users WHERE name LIKE 'a%'
SELECT name FROM users WHERE name LIKE 'b%'
SELECT name FROM users WHERE name LIKE 'c%'
etc...
The presentation layer could run each query and then populate a table appropriately. Even better would be to have the presentation layer just run this query:
表示层可以运行每个查询，然后适当地填充表。更好的是让表示层只运行这个查询：
SELECT name FROM users
And then appropriately sort and display the data, which is probably going to be less expensive than multiple scans on your users table by SQL Server.
然后对数据进行适当的排序和显示，这可能比 SQL Server 对用户表进行多次扫描的成本更低。
回答by Marcelo
You can use CASE (https://msdn.microsoft.com/en-us/library/ms181765.aspx)
您可以使用 CASE ( https://msdn.microsoft.com/en-us/library/ms181765.aspx)
This Query should work:
这个查询应该有效：
SELECT
CASE WHEN users.name like 'a%' THEN users.name ELSE NULL END AS NameA,
CASE WHEN users.name like 'b%' THEN users.name ELSE NULL END AS NameB,
CASE WHEN users.name like 'c%' THEN users.name ELSE NULL END AS NameC,
CASE WHEN users.name like 'd%' THEN users.name ELSE NULL END AS NameD
FROM users
回答by Kami
Look at this post, it is very close to your problem. Itzik Ben-Gan | SQL Server Pro
看看这个帖子，它非常接近你的问题。 Itzik Ben-Gan | SQL Server 专业版
