SQL Server语言 - 透视/转置表结果

Question

我想对SQL表的结果进行转置，使行变成列，按特定维度分组。我在下面提供了代码来创建我目前拥有的表(#input_table)和我需要获得的表(#output_table)。有人可以帮助，如果这是可能的在SQL服务器和如何做?

drop table if exists #input_table
create table #input_table
(
GroupNo varchar(10),
Keyword varchar(10),
X_Top float,
Y_Top float,
X_Bottom float, 
Y_Bottom float
)
insert into #input_table values ('A1234', 'A', 1, 1, 2, 2)
insert into #input_table values ('A1234', 'B', 10, 10, 20, 20)
insert into #input_table values ('B5678', 'A', 8, 3, 5, 5)
insert into #input_table values ('B5678', 'B', 12, 18, 26, 27)

drop table if exists #output_table
create table #output_table
(
GroupNo varchar(10),
A_X_Top float,
A_Y_Top float,
A_X_Bottom float,   
A_Y_Bottom float,
B_X_Top float,
B_Y_Top float,
B_X_Bottom float,   
B_Y_Bottom float
)
insert into #output_table values ('A1234', 1, 1, 2, 2, 10, 10, 20, 20)
insert into #output_table values ('B5678', 8, 3, 5, 5, 12, 18, 26, 27)
select * from #input_table
select * from #output_table

Answer 1

我想这应该可以工作:

select 
GroupNo
,max(case when Keyword = 'A' then X_top end) as A_X_top
,max(case when Keyword = 'A' then Y_top end) as A_Y_top
,max(case when Keyword = 'A' then X_bottom end) as A_X_bottom
,max(case when Keyword = 'A' then Y_bottom end) as A_Y_bottom
,max(case when Keyword = 'B' then X_top end) as B_X_top
,max(case when Keyword = 'B' then Y_top end) as B_Y_top
,max(case when Keyword = 'B' then X_bottom end) as B_X_bottom
,max(case when Keyword = 'B' then Y_bottom end) as B_Y_bottom
from
#input_table 
group by GroupNo