sql - 有条件地选择多行并将其插入 SQL Server 中的单行

Question

我正在寻找有人指出正确的方向如何在不使用光标的情况下遍历结果集并插入表中。甚至不知道如何问这个。

选择查询的结果集：

------------------------------------------------------
ID |  Name  |  CreatedBy  | CreatedDate 
------------------------------------------------------
1     A     John            2018-04-30
1     B     Sam             2018-04-20
2     A     John            2018-04-18
-------------------------------------------------------

插入表：

---------------------------------------------------------------------------------------------------------
ID  |  A_CreatedBy | A_CreatedDate  | B_CreatedBy  |    B_CreatedDate |  C_CreatedBy  | C_CreatedDate 
---------------------------------------------------------------------------------------------------------
1     John          2018-04-30        Sam                   2018-04-20       NULL           NULL
2     John          2018-04-18        NULL              NULL             NULL           NULL
---------------------------------------------------------------------------------------------------------

任何帮助，将不胜感激。

标签： sqlsql-server-2012

Answer 1

您可以按如下方式使用条件聚合：

select ID,
       max(case when name = 'A' then createdby end) as a_createdby,
       max(case when name = 'A' then CreatedDate end) as a_CreatedDate,
       max(case when name = 'B' then createdby end) as b_createdby,
       max(case when name = 'B' then CreatedDate end) as b_CreatedDate,
       max(case when name = 'C' then createdby end) as c_createdby,
       max(case when name = 'C' then CreatedDate end) as c_CreatedDate
from (your_query) t
group by ID