对查询结果进行行转列操作要用到的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

【编辑推荐】

SQL数据库压缩语句简介

教您用事务一次处理多条SQL语句

SQL语句中CASE WHEN的使用实例

巧用GO将多次重复执行SQL语句

父子分类关系查询使用的SQL语句介绍