SQL Server - Pivot/transpose the table results-CodePudding

I want to transpose the results of a SQL table such that the rows become columns, grouped by a particular dimension. I've provided below the code to create the table I currently have (#input_table) and the table I need to get (#output_table). Can someone please help if this is possible to do in SQL Server and how?

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

CodePudding user response：

I guess this should work:

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