SQL将行转置为列 [英] SQL Transpose row to columns

问题描述

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

I am trying to transpose rows to columns but I didn't find any good answers.

这是我想要的例子:

输入表:

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

---

输出将是:

---

OUTPUT will be:

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

推荐答案

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

You need to enumerate the values to pivot them:

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;

这篇关于SQL将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！