SQL合并具有相同ID但列值不同的两行(Oracle)

本文介绍了SQL合并具有相同ID但列值不同的两行(Oracle)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

当它们具有相同的ID但列值不同时，我试图将不同的行合并为一个.

I am trying to merge different rows into one when they have the same id but different column values.

例如:

(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

或

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.

推荐答案

请先阅读我的评论-除非仅用于报告目的，否则您甚至都不要考虑这样做，并且您想了解如何做到这一点用普通的SQL(与正确的解决方案相对，正确的解决方案是使用报表工具完成这项工作).

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).

第二种格式最简单，特别是如果您不关心颜色出现的顺序:

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 null表示随机排序.如果要按其他顺序订购，请在order by和listagg()中使用.例如，要按字母顺序排列颜色，您可以说within group (order by colour).

order by null means order randomly. If you want to order by something else, use that in order by with listagg(). For example, to order the colors alphabetically, you could say within group (order by colour).

对于第一种格式，您需要对列数进行先验限制，其处理方式取决于您所使用的Oracle版本(在此处和之后发布的每个问题中都应始终包含该限制)其他讨论区).这个概念称为透视".从版本11开始，Oracle具有您可以使用的显式PIVOT运算符.

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.