SQL合并具有相同ID但列值不同的两行(Oracle) [英] SQL Merge two rows with same ID but different column values (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.

这篇关于SQL合并具有相同ID但列值不同的两行(Oracle)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！