T-SQL从一个表变化的数据，并插入到另一个表

Question

我的基表是这样的：

ColumnA|ColumnB 
--------------- 
    A | C1 
    A | C2 
    A | C3 
    B | C1 
    B | C3 
    C | C4 

我想从基础表中读取记录，并将其写入到见下表：

ColumnA | C1 | C2 | C3 | C4 
---------------------------- 
    A | Y | Y | Y | N 
    B | Y | N | Y | N 
    C | N | N | N | Y 

我不想使用游标，但我不知道这是否可能。

感谢

Answer 1

一个（通常快速）的方法是group by：

insert NewTable (ColumnA, C1, C2, C3, C4) 
select ColumnA 
,  IsNull(max(case when ColumnB = 'C1' then 'Y' end), 'N') 
,  IsNull(max(case when ColumnB = 'C2' then 'Y' end), 'N') 
,  IsNull(max(case when ColumnB = 'C3' then 'Y' end), 'N') 
,  IsNull(max(case when ColumnB = 'C4' then 'Y' end), 'N') 
from OldTable 
group by 
     ColumnA 

另一种方式是子查询，如：

insert NewTable (ColumnA, C1, C2, C3, C4) 
select src.ColumnA 
,  case when exists (select * from OldTable ot 
          where ot.ColumnA = src.ColumnA and ot.ColumnB = 'C1') 
        then 'Y' else 'N' end 
,  case when exists (select * from OldTable ot 
          where ot.ColumnA = src.ColumnA and ot.ColumnB = 'C2') 
        then 'Y' else 'N' end 
,  case when exists (select * from OldTable ot 
          where ot.ColumnA = src.ColumnA and ot.ColumnB = 'C3') 
        then 'Y' else 'N' end 
,  case when exists (select * from OldTable ot 
          where ot.ColumnA = src.ColumnA and ot.ColumnB = 'C4') 
        then 'Y' else 'N' end 
from (
     select distinct ColumnA 
     from OldTable 
     ) src 

或者，改编自克里斯潜水员的回答，用pivot：

select ColumnA 
,  case when C1 > 0 then 'Y' else 'N' end C1 
,  case when C2 > 0 then 'Y' else 'N' end C2 
,  case when C3 > 0 then 'Y' else 'N' end C3 
,  case when C4 > 0 then 'Y' else 'N' end C4 
from OldTable src 
pivot (
     count(ColumnB) 
     for ColumnB IN ([C1], [C2], [C3], [C4]) 
     ) pvt 

Answer 2

假设你可以选择你喜欢的信息，那么你可以编写插件作为选择的结果。

Answer 3

看看PIVOT命令。从那里，你可以做一个INSERT INTO ... SELECT ...

SELECT ColumnA, [C1], [C2], [C3], [C4] 
FROM (SELECT * FROM table) t 
PIVOT 
(
Count(ColumnB) 
FOR ColumnB IN ([C1], [C2], [C3], [C4]) 
) As Pvt