SQL将行转置为列

小编典典

SQL将行转置为列

sql

我正在尝试将行转置为列，但是没有找到任何好的答案。

这是我想要的示例：

输入表：

TABLE A    
ID | NAME     
1   | BOB    
2   | JIM    
3   | ROB

TABLE B

ID  | CLUB
1   | 2    
1   | 3    
1   | 4    
2   | 2    
2   | 1    
3   | 5

输出将是：

ID  | CLUB1 | CLUB2 | CLUB3    
1   | 2     | 3     | 4    
2   | 2     | 1     |    
3   | 5     |       |

阅读 195

2021-03-23

共1个答案

小编典典

您需要枚举值以对其进行透视：

select id,
       max(case when seqnum = 1 then club end) as club_1,
       max(case when seqnum = 2 then club end) as club_2,
       max(case when seqnum = 3 then club end) as club_3
from (select b.*,
             row_number() over (partition by id order by club) as seqnum
      from b
     ) b
group by id;

2021-03-23