如何根据ID将两行合并为同一表上的一行

我有一个看起来像这样的表:

+------------+-------------+-------------+
carId | infoId | infoTitle | Description |  
+------------+-------------+-------------+

我对同一辆车有一些描述:

+------------+-------------+-------------+
carId | infoId | infoTitle | Description |  
+------------+-------------+-------------+

1     | 11     | Wheels    | nice wheels |
1     | 12     | Paint     | some nice red painting |

我需要将这两个信息加入同一辆车。输出将如下所示:

+------------+-------------+-------------+---------+------------+--------------+
carId | infoId | infoTitle | Description | infoId2 | infoTitle2 | Description2 |  
+------------+-------------+-------------+---------+------------+--------------+

1     | 11     | Wheels    | nice wheels | 12      | Paint      | some nice red painting |

问题是我没有与同一辆车有关的信息的固定编号,因此我需要一个查询,该查询将新列添加到与该车有关的每个信息中。

我试图用SELECT DISTINCT做一些事情,但是显然没有用。

评论
  • Tracy
    Tracy 回复

    您可以使用条件聚合:

    select carid,
           max(case when seqnum = 1 then infoid end) as infoid_1,
           max(case when seqnum = 1 then infotitle end) as infotitle_1,
           max(case when seqnum = 1 then description end) as description_1,
           max(case when seqnum = 2 then infoid end) as infoid_2,
           max(case when seqnum = 2 then infotitle end) as infotitle_2,
           max(case when seqnum = 2 then description end) as description_2
    from (select t.*,
                 row_number() over (partition by carid order by infoid) as seqnum
          from t
         ) t
    group by carid;