SQL 合并 ID 相同但列值不同的两行 (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40831690/
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 Merge two rows with same ID but different column values (Oracle)
提问by LEJ
I am trying to merge different rows into one when they have the same id but different column values.
当它们具有相同的 id 但不同的列值时，我试图将不同的行合并为一个。
For example :
例如 ：
(table1)
id colour
1 red
1 blue
2 green
2 red
I would like this to be combine so that the result is :
我希望将其结合起来，结果是：
id colour1 colour2
1 red blue
2 green red
Or
或者
id colour
1 red, blue
2 green, red
Or any other variation of the above so that the rows are joined together some way.
或上述任何其他变体，以便行以某种方式连接在一起。
Any help would be appreciated! Thanks in advance.
任何帮助，将不胜感激！提前致谢。
采纳答案by mathguy
Please read my Comment first - you shouldn't even think about doing this unless it is ONLY for reporting purposes, and you want to see how this can be done in plain SQL (as opposed to the correct solution, which is to use your reporting tool for this job).
请先阅读我的评论 - 您甚至不应该考虑这样做，除非它仅用于报告目的，并且您想了解如何在普通 SQL 中完成此操作（而不是正确的解决方案，即使用您的报告这项工作的工具）。
The second format is easiest, especially if you don't care about the order in which the colors appear:
第二种格式最简单，尤其是如果您不关心颜色出现的顺序时：
select id, listagg(colour, ', ') within group (order by null)
from table1
group by id
order by nullmeans order randomly. If you want to order by something else, use that in order bywith listagg(). For example, to order the colors alphabetically, you could say within group (order by colour).
order by null表示随机排序。如果您想按其他方式订购，请在order bywith中使用listagg()。例如，要按字母顺序排列颜色，您可以说within group (order by colour).
For the first format, you need to have an a priori limit on the number of columns, and how you do it depends on the version of Oracle you are using (which you should always include in every question you post here and on other discussion boards). The concept is called "pivoting"; since version 11, Oracle has an explicit PIVOT operator that you can use.
对于第一种格式，您需要对列数有一个先验限制，并且您如何做到这一点取决于您使用的 Oracle 版本（您应该始终将其包含在您在此处和其他讨论板上发布的每个问题中）。这个概念叫做“旋转”；从版本 11 开始，Oracle 有一个您可以使用的显式 PIVOT 运算符。
回答by Brian DeMilia
The following would solve your problem in the first of the two ways that you proposed. Listaggis what you would use to solve it the second of the two ways (as pointed out in the other answer):
以下将通过您提出的两种方式中的第一种解决您的问题。Listagg是你用来解决它的两种方法中的第二种（如另一个答案中所指出的）：
select id,
min(decode(rn,1,colour,null)) as colour1,
min(decode(rn,2,colour,null)) as colour2,
min(decode(rn,3,colour,null)) as colour3
from (
select id,
colour,
row_number() over(partition by id order by colour) as rn
from table1
)
group by id;
In this approach, you need to add additional case statements up to the maximum number of possible colors for a given ID (this solution is not dynamic).
在这种方法中，您需要添加额外的 case 语句，直至给定 ID 的最大可能颜色数（此解决方案不是动态的）。
Additionally, this is putting the colors into color1, color2, etc. based on the alphabetical order of the color names. If you prefer a random order, or some other order, you need to change the order by.
此外，这是根据颜色名称的字母顺序将颜色放入 color1、color2 等中。如果您更喜欢随机顺序或其他顺序，则需要更改order by.
回答by Manjunath Akalawadi
Try this, it works for me:
试试这个，它对我有用：
Here studentis the name of the table and studentIdis a column. We can merge all subjects to the particular student using GROUP_CONCAT.
这student是表的名称，studentId是一列。我们可以使用 将所有科目合并到特定学生GROUP_CONCAT。
SELECT studentId, GROUP_CONCAT(subjects) FROM student
