Oracle SQL - 将 N 行的列值转换为 1 行中的 N 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9319997/
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
Oracle SQL - Convert N rows' column values to N columns in 1 row
提问by Jeremy
The trick with this compared to the other questions (e.g. "Oracle convert rows to columns") is that my column values are arbitrary strings, rather than something I can use with decode. Take this query:
与其他问题（例如“Oracle 将行转换为列”）相比，这个技巧是我的列值是任意字符串，而不是我可以与解码一起使用的字符串。拿这个查询：
The description table here maps people's names to descriptions, but each person can have multiple descriptions e.g. "wears a hat" or "is tall".
此处的描述表将人名映射到描述，但每个人可以有多个描述，例如“戴帽子”或“很高”。
Select firstName, lastName,
(Select description from descriptions --This can return any number of rows (0 or more)
where description.firstName = people.firstName
and description.lastName = people.lastName
and rownum <= 3)
from people
where age >= 25;
I would want an output like this:
我想要这样的输出：
FIRSTNAME LASTNAME DESCRIPTION1 DESCRIPTION2 DESCRIPTION3
Jeremy Smith Tall Confused (null)
Anne Smith (Null) (Null) (Null)
Mark Davis Short Smart Strong
In the case of less than 3 descriptions, I want nulls there. In the case of more than 3 descriptions, I want to just leave them out.
在少于 3 个描述的情况下，我希望那里有空值。在超过 3 个描述的情况下，我只想将它们排除在外。
I am using Oracle 11.1. Can this be done efficiently?
我正在使用 Oracle 11.1。这可以有效地完成吗？
回答by Justin Cave
Assuming that you don't care what order the descriptions are returned in (i.e. Jeremy Smith could just as correctly have a Description1or "Confused" and a Description2of "Tall"), you just need to pivot on the row number. If you care about the order the descriptions are returned in, you can add an ORDER BYclause to the window function in the ROW_NUMBERanalytic function
假设您不关心返回描述的顺序（即 Jeremy Smith 可以正确地使用 aDescription1或“Confused”和 aDescription2为“Tall”），您只需要以行号为中心。如果您关心描述返回的顺序，您可以ORDER BY在ROW_NUMBER解析函数中的窗口函数中添加一个子句
SELECT firstName,
lastName,
MAX( CASE WHEN rn = 1 THEN description ELSE NULL END ) description1,
MAX( CASE WHEN rn = 2 THEN description ELSE NULL END ) description2,
MAX( CASE WHEN rn = 3 THEN description ELSE NULL END ) description3
FROM (SELECT firstName,
lastName,
description,
row_number() over (partition by lastName, firstName) rn
FROM descriptions
JOIN people USING (firstName, lastName)
WHERE age >= 25)
GROUP BY firstname, lastname
As an aside, I'm hoping that you're actually storing a birth date and computing the person's age rather than storing the age and assuming that people are updating their age every year.
顺便说一句，我希望您实际上是在存储出生日期并计算此人的年龄，而不是存储年龄并假设人们每年都在更新他们的年龄。
回答by Naveen Venkat
I have tried this option, but it says we should give order by clause inside row analytics function as shown below,
我已经尝试过这个选项，但它说我们应该在行分析函数中给出 order by 子句，如下所示，
row_number() over (partition by lastName, firstName order by lastName, firstName) rn
It works fine for my scenario when i put order by clause.
当我放置 order by 子句时，它适用于我的场景。
My scenario is user details are in table A, usergroups are in table C, and association between users and usergroups in table B. One user can have multiple usergroups. I need to get results with username with multiple usergroups in a single row
我的场景是用户详细信息在A表中，用户组在C表中，用户和用户组之间的关联在B表中。一个用户可以有多个用户组。我需要在一行中使用具有多个用户组的用户名获得结果
**
**
Query:
询问：
**
**
SELECT username,
MAX( CASE WHEN rn = 1 THEN ugroup ELSE NULL END ) usergroup1,
MAX( CASE WHEN rn = 2 THEN ugroup ELSE NULL END ) usergroup2,
MAX( CASE WHEN rn = 3 THEN ugroup ELSE NULL END ) usergroup3,
MAX( CASE WHEN rn = 4 THEN ugroup ELSE NULL END ) usergroup4,
MAX( CASE WHEN rn = 5 THEN ugroup ELSE NULL END ) usergroup5,
from (
select
a.user_name username,
c.name ugroup,
row_number() over (partition by a.user_name order by a.user_name) rn
from users a,
usergroupmembership b,
usergroups c
where a.USER_NAME in ('aegreen',
'esportspau'
)
and a.user_id= b.user_id
and b.group_id=c.group_id
)group by uname;
**
**
Query Result
查询结果
**
**
USERNAME USERGROUP1 USERGROUP2 USERGROUP3 USERGROUP4 USERGROUP5
aegreen US_GOLF (null) (null) (null) (null)
esportspau EMEA - FSERVICE USER_ES_ES EMEA-CR-ONLY (null) (null)
