代码之家  ›  专栏  ›  技术社区  ›  Tyvain

Oracle SQL:如何合并n行并在结果中创建其他列?

  •  1
  • Tyvain  · 技术社区  · 6 年前

    表:

    id race
    1  elf
    1  troll
    2  lizard
    2  elf
    2  human    
    3  dwarf
    

    我正在寻找输出以下内容的请求:

    id race1   race2   race3
    1  elf     troll   
    2  lizard  elf     human
    3  dwarf
    

    如果比较容易的话,可以有n个比赛或给定的最大比赛次数

    1 回复  |  直到 6 年前
        1
  •  1
  •   Gordon Linoff    6 年前

    select :

    select id,
           max(case when seqnum = 1 then race end) as race_1,
           max(case when seqnum = 2 then race end) as race_2,
           max(case when seqnum = 3 then race end) as race_3,
           max(case when seqnum = 4 then race end) as race_4,
           max(case when seqnum = 5 then race end) as race_5
    from (select t.*,
                 row_number() over (partition by id order by id) as seqnum
          from t
         ) t
    group by id;