查詢以根據第一個表的列將記錄從一個表插入另一個表

我有一個結構如下的表：

Table(customer_Num  int , Channel_Id  Varchar(50) ,  Branch_Code  int)

該表的一些範例數據是：

                 Table
__________________________________________
customer_Num  |  Channel_Id  |  Branch_Code 
   1         |    'I'       |     1000
   1         |    'Y'       |     1001
   1         |    'L'       |     1002

我在輸出中想要的最終結果是：

                         Result_table
__________________________________________________________
customer_Num  | I_branch_Code |  Y_branch_Code  | L_Branch_Code
   1               1000             1001             1002

有沒有比使用連續Insert 查詢更好的解決方案？因為有了這個查詢，我的最終表中會有很多空值！而且我也不會在一條記錄中得到結果。

insert into Result_table (customer_Num  ,I_branch_Code )
select customer_Num   , Branch_Code 
from Table
where Channel_Id   = 'I';

insert into Result_table (customer_Num  ,Y_branch_Code  )
select customer_Num   , Branch_Code 
from Table
where Channel_Id   = 'Y';


insert into Result_table (customer_Num  ,L_Branch_Code)
select customer_Num   , Branch_Code 
from Table
where Channel_Id  = 'L';

您正在尋找PIVOT子句。您可以在這個db<>fiddle中看到一個工作範例。

查詢是：

SELECT customer_num
 ,p.I AS I_Branch_Code
 ,p.Y AS Y_Branch_Code
 ,p.L AS L_Branch_Code
FROM
(
 SELECT customer_num
   ,Channel_Id
   ,Branch_code
 FROM [table]
)s
PIVOT
(
 MAX(Branch_Code) FOR Channel_Id IN ([I], [Y], [L])
) p

引用自：https://dba.stackexchange.com/questions/267330