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             |
+----+---------------------++---------------------+

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

像图片

标签： sqlsql-server

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;