拆分到其他表列的 MS SQL Server 查询

Question

我有两张桌子：

table1：

Manager | USER 
--------+------
John    | Tom  
John    | David
John    | Marry
James   | Henry

table2：现在是空的，但是必须是这样的：

Manager | User1 | User2 | User3
--------+-------+-------+-------
John    | Tom   | David | Marry
James   | Henry | null  | null 

是否可以？

Answer 1

如果table2结构如您所展示的那样固定，您可以通过如下查询将预期结果插入其中：

INSERT INTO table2 (Manager, User1, User2, User3) 
SELECT
  Manager,
  MAX(CASE WHEN seq = 1 THEN [USER] END) AS User1,
  MAX(CASE WHEN seq = 2 THEN [USER] END) AS User2,
  MAX(CASE WHEN seq = 3 THEN [USER] END) AS User3
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY Manager ORDER BY [USER]) AS seq
  FROM table1) t
GROUP BY
  Manager;

但是，如果table2结构不固定，则需要使用动态 SQL。