sqlserver行转列

原创于 2011-08-30 09:31:08 发布 · 259 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #sql #table #insert #object

本文介绍了SQL操作中创建表、插入数据、数据筛选及聚合查询的方法，并通过具体实例展示了如何进行复杂的数据处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if(object_id('qa') > 0)
   drop table qa
go
create table qa(
   qtcode varchar(100),
   simpleTime varchar(20),
   water decimal(18,2)
)
insert into qa
select 'a','09:10',11 union all
select 'a','10:01',22 union all
select 'a','10:20',33 union all
select 'a','10:30',44 union all
select 'a','10:40',55 union all
select 'b','08:10',12 union all
select 'b','10:01',13 union all
select 'b','10:02',14 union all
select 'b','10:20',15

select * from qa

declare @sql varchar(3000)
set @sql = 'select qtcode ';
select @sql = @sql + ' ,(case simpleTime when '''+simpleTime+''' then water else 0 end) ['+simpleTime+']'
from (select distinct simpleTime from qa) as a
set @sql = @sql+' from qa '
exec(@sql)

declare @sql1 varchar(3000)
set @sql1 = 'select qtcode ';
select @sql1 = @sql1 + ' ,max(case simpleTime when '''+simpleTime+''' then water else 0 end) ['+simpleTime+']'
from (select distinct simpleTime from qa ) as a
set @sql1 = @sql1+' from qa group by qtcode '
exec(@sql1)