對查詢結果進行行轉列操作要用到的SQL語句
在使用SQL數據庫的時候，進行查詢后，如何對查詢結果進行行轉列操作呢？下面就將為您介紹執行該操作要用到的SQL語句，供您參考，希望對您學習SQL語句有所幫助。
測試數據：
code price
'MCU0-3' 15
'MCU0-3' 20
'MCU0-3' 22
'MCU3-15' 17
'MCU3-15' 16
'MCU3-15' -10
'MCU3-15' 50
'MCU3-27' 99
'MCU3-27' 96
'MCU3-27' 54
'MCU3-27' 14
'MCU3-27' 46
'MCU3-27' 86
結果：
MCU0-3 MCU3-15 MCU3-27
15 17 99
20 16 96
22 -10 54
null 50 14
null null 46
null null 86
SQL語句：
drop table [dbo].[RowToCol];
CREATE TABLE [dbo].[RowToCol]
(
[code] varchar(50) NULL,
[price] varchar(50) NULL,
)
ON [PRIMARY];
select * from [RowToCol];
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU0-3',15);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU0-3',20);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU0-3',22);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-15',17);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-15',16);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-15',-10);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-15',50);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-27',99);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-27',96);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-27',54);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-27',14);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-27',46);
insert into [dbo].[RowToCol] ([code],[price]) values ('MCU3-27',86);
drop table #T1;
drop table #T2;
drop table #T3;
select ID=IDENTITY(INT,1,1),[price] into #T1 from [RowToCol] where [code]='MCU0-3';
select ID=IDENTITY(INT,1,1),[price] into #T2 from [RowToCol] where [code]='MCU3-15';
select ID=IDENTITY(INT,1,1),[price] into #T3 from [RowToCol] where [code]='MCU3-27';
select t1.price as 'MCU0-3', t2.price as 'MCU3-15',t3.price as 'MCU3-27' from #T1 t1 FULL OUTER JOIN #T2 t2 on t1.id = t2.id FULL OUTER JOIN #T3 t3 on t2.id = t3.id
【編輯推薦】
