SQL语句对查询结果进行行转列

最新推荐文章于 2024-12-05 18:05:57 发布

weixin_33770878

最新推荐文章于 2024-12-05 18:05:57 发布

阅读量573

点赞数

文章标签：嵌入式

原文链接：http://www.cnblogs.com/ruanzb/archive/2010/06/10/1755969.html

版权

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

转载于:https://www.cnblogs.com/ruanzb/archive/2010/06/10/1755969.html