sql - 像枢轴一样将行转换为列

Question

表是:

  +----+------+
  | Id | Name |
  +----+------+    
  | 1  | aaa  |
  | 1  | bbb  |
  | 2  | ccc  |
  | 2  | ddd  |
  | 3  | eee  |
  +----+------+

所需输出:

+----+---------------------++---------------------+
| Id |        colum1       |   column2            |
+----+---------------------+ +--------------------+ 
|  1 | aaa                 | |   bbb              |
+----+---------------------++---------------------+
+----+---------------------+ +--------------------+ 
|  2 | ccc                | |   ddd               |
+----+---------------------++---------------------+
+----+---------------------+ +--------------------+ 
|  3 | eee                 | |   null             |
+----+---------------------++---------------------+

我一直在尝试使用“a”和“pivot”，但这似乎不是正确的方式，如果我有多个 id，我想要一列

喜欢图片

Answer 1

如果您的名称数量有限，您可以使用 row_number() 进行聚合，否则您需要使用动态 SQL:

select id, 
       max(case when seq = 1 then name end) as col1,
       max(case when seq = 2 then name end) as col2,
       max(case when seq = 3 then name end) as col3,
       . . .
from (select t.*, row_number() over (partition by id order by name) as seq
      from table t
     ) t
group by id;