--生成测试数据 create table #t([desc] varchar(10),dotime varchar(20),data numeric(5,1)) insert into #t select '浓度','08:00',2.3 insert into #t select '浓度','09:00',2.5 insert into #t select '浓度','10:00',2.6 insert into #t select '浓度','11:00',2.1 insert into #t select 'PH值','08:00',2 insert into #t select 'PH值','09:00',3.6 insert into #t select 'PH值','10:00',5 insert into #t select 'PH值','11:00',6 --执行查询 declare @s varchar(8000) declare @i int set @s = '' set @i = 0set rowcount 16 select @i = @i + 1, @s = @s + ',[data'+rtrim(@i)+']=max(case dotime when '''+dotime+''' then data end)' from #t group by dotime set rowcount 0if @i <= 12 set @s = @s+',[avg]=avg(data),[max]=max(data),[min]=min(data),极差=max(data)-min(data)'set @s = 'select [desc]'+@s+' from #t group by [desc]' exec(@s) --输出结果 desc data1 data2 data3 data4 avg max min 极差 ------ ----- ----- ----- ----- ----- ----- ----- ----- PH值 2.0 3.6 5.0 6.0 4.150 6.0 2.0 4.0 浓度 2.3 2.5 2.6 2.1 2.375 2.6 2.1 0.5
--生成测试数据 create table #t([desc] varchar(10),dotime varchar(20),data numeric(5,1)) insert into #t select '浓度','08:00',2.3 insert into #t select '浓度','09:00',2.5 insert into #t select '浓度','10:00',2.6 insert into #t select '浓度','11:00',2.1 insert into #t select 'PH值','08:00',2 insert into #t select 'PH值','09:00',3.6 insert into #t select 'PH值','10:00',5 insert into #t select 'PH值','11:00',6 --创建存储过程 create procedure sp_test as begin declare @s varchar(8000) declare @i int set @s = '' set @i = 0
set rowcount 16 select @i = @i + 1, @s = @s + ',[data'+rtrim(@i)+']=max(case dotime when ''' + dotime + ''' then data end)' from #t group by dotime set rowcount 0
if @i <= 12 set @s = @s+',[avg]=avg(data),[max]=max(data)' +',[min]=min(data),极差=max(data)-min(data)'
set @s = 'select [desc]'+@s+' from #t group by [desc]' exec(@s) end go --执行查询 exec sp_test --输出结果 desc data1 data2 data3 data4 avg max min 极差 ------ ----- ----- ----- ----- ----- ----- ----- ----- PH值 2.0 3.6 5.0 6.0 4.150 6.0 2.0 4.0 浓度 2.3 2.5 2.6 2.1 2.375 2.6 2.1 0.5
--生成测试数据 create table tb([desc] varchar(10),dotime varchar(20),data numeric(5,1)) insert into tb select '浓度','08:00',2.3 insert into tb select '浓度','09:00',2.5 insert into tb select '浓度','10:00',2.6 insert into tb select '浓度','11:00',2.1 insert into tb select 'PH值','08:00',2 insert into tb select 'PH值','09:00',3.6 insert into tb select 'PH值','10:00',5 insert into tb select 'PH值','11:00',6 insert into tb select 'PH值','12:00',2 insert into tb select 'PH值','13:00',3.6 insert into tb select 'PH值','14:00',5 insert into tb select 'PH值','15:00',6 insert into tb select 'PH值','16:00',2 insert into tb select 'PH值','17:00',3.6 insert into tb select 'PH值','18:00',5 insert into tb select 'PH值','19:00',6 insert into tb select 'PH值','20:00',2 insert into tb select 'PH值','21:00',3.6 insert into tb select 'PH值','22:00',5 insert into tb select 'PH值','23:00',66 insert into tb select 'PH值','00:00',666 go--创建存储过程 create procedure p_test as set nocount on declare @cols int,@s varchar(8000),@i varchar(10),@b varchar(8000) select top 1 @cols=12, --一条记录最多12列 @s='', @i=case when count(*)>@cols then @cols-1 else count(*)-1 end, @b=', [avg]=case when (a.id-b.id)/'+rtrim(@cols)+'=b.gid then cast(b.[avg] as varchar) else '''' end, [max]=case when (a.id-b.id)/'+rtrim(@cols)+'=b.gid then cast(b.[max] as varchar) else '''' end, [min]=case when (a.id-b.id)/'+rtrim(@cols)+'=b.gid then cast(b.[min] as varchar) else '''' end, [极差]=case when (a.id-b.id)/'+rtrim(@cols)+'=b.gid then cast(b.[max]-b.[min] as varchar) else '''' end from # a left join( select [desc], [avg]=cast(avg(cast(data as numeric(5,1))) as decimal(5,1)), [max]=cast(max(cast(data as numeric(5,1))) as decimal(5,1)), [min]=cast(min(cast(data as numeric(5,1))) as decimal(5,1)), gid=(max(id)-min(id))/'+rtrim(@cols)+', id=min(id) from # group by [desc] )b on a.[desc]=b.[desc] -- and (a.id-b.id)/'+rtrim(@cols)+'=b.gid group by a.[desc],(a.id-b.id)/'+rtrim(@cols)+',b.gid,b.[avg],b.[max],b.[min]' from tb group by [desc] order by count(*) desc while @i>=0 select @s=',[data'+rtrim(@i+1)+']=max(case (a.id-b.id)%'+rtrim(@cols)+' when '+@i+' then data else '''' end)'+@s, @i=@i-1
exec(' select id=identity(int,1,1),[desc],dotime,data=cast(data as varchar) into # from tb order by [desc]--,dotime select a.[desc]'+@s+@b)
浓度 2.3 2.5 2.6 2.1 ... 2.3 2.3 2.6 2.9 3
PH值 2 3.6 5 6 ... 5 4 5 1 2
浓度 3.3 3.2
PH值 5 4 所谓换页,是列中数据放到下一页。不知道存储过程能不能做到。
create table #t([desc] varchar(10),dotime varchar(20),data numeric(5,1))
insert into #t select '浓度','08:00',2.3
insert into #t select '浓度','09:00',2.5
insert into #t select '浓度','10:00',2.6
insert into #t select '浓度','11:00',2.1
insert into #t select 'PH值','08:00',2
insert into #t select 'PH值','09:00',3.6
insert into #t select 'PH值','10:00',5
insert into #t select 'PH值','11:00',6
--执行查询
declare @s varchar(8000)
declare @i int
set @s = ''
set @i = 0set rowcount 16
select
@i = @i + 1,
@s = @s + ',[data'+rtrim(@i)+']=max(case dotime when '''+dotime+''' then data end)'
from
#t
group by
dotime
set rowcount 0if @i <= 12
set @s = @s+',[avg]=avg(data),[max]=max(data),[min]=min(data),极差=max(data)-min(data)'set @s = 'select [desc]'+@s+' from #t group by [desc]'
exec(@s)
--输出结果
desc data1 data2 data3 data4 avg max min 极差
------ ----- ----- ----- ----- ----- ----- ----- -----
PH值 2.0 3.6 5.0 6.0 4.150 6.0 2.0 4.0
浓度 2.3 2.5 2.6 2.1 2.375 2.6 2.1 0.5
create table #t([desc] varchar(10),dotime varchar(20),data numeric(5,1))
insert into #t select '浓度','08:00',2.3
insert into #t select '浓度','09:00',2.5
insert into #t select '浓度','10:00',2.6
insert into #t select '浓度','11:00',2.1
insert into #t select 'PH值','08:00',2
insert into #t select 'PH值','09:00',3.6
insert into #t select 'PH值','10:00',5
insert into #t select 'PH值','11:00',6
--创建存储过程
create procedure sp_test
as
begin
declare @s varchar(8000)
declare @i int
set @s = ''
set @i = 0
set rowcount 16
select
@i = @i + 1,
@s = @s + ',[data'+rtrim(@i)+']=max(case dotime when '''
+ dotime + ''' then data end)'
from
#t
group by
dotime
set rowcount 0
if @i <= 12
set @s = @s+',[avg]=avg(data),[max]=max(data)'
+',[min]=min(data),极差=max(data)-min(data)'
set @s = 'select [desc]'+@s+' from #t group by [desc]'
exec(@s)
end
go
--执行查询
exec sp_test
--输出结果
desc data1 data2 data3 data4 avg max min 极差
------ ----- ----- ----- ----- ----- ----- ----- -----
PH值 2.0 3.6 5.0 6.0 4.150 6.0 2.0 4.0
浓度 2.3 2.5 2.6 2.1 2.375 2.6 2.1 0.5
这样做出来在12个数据内,是没问题的,超过12个,后面的平均值等都没了,超过16个的数据,也都不显示出来了。我的意思是希望超过12个数据,则后面的数据及平均值等放到下一页去,我再试一下!
非常谢谢子陌红尘!^-^
create table tb([desc] varchar(10),dotime varchar(20),data numeric(5,1))
insert into tb select '浓度','08:00',2.3
insert into tb select '浓度','09:00',2.5
insert into tb select '浓度','10:00',2.6
insert into tb select '浓度','11:00',2.1
insert into tb select 'PH值','08:00',2
insert into tb select 'PH值','09:00',3.6
insert into tb select 'PH值','10:00',5
insert into tb select 'PH值','11:00',6
insert into tb select 'PH值','12:00',2
insert into tb select 'PH值','13:00',3.6
insert into tb select 'PH值','14:00',5
insert into tb select 'PH值','15:00',6
insert into tb select 'PH值','16:00',2
insert into tb select 'PH值','17:00',3.6
insert into tb select 'PH值','18:00',5
insert into tb select 'PH值','19:00',6
insert into tb select 'PH值','20:00',2
insert into tb select 'PH值','21:00',3.6
insert into tb select 'PH值','22:00',5
insert into tb select 'PH值','23:00',66
insert into tb select 'PH值','00:00',666
go--创建存储过程
create procedure p_test
as
set nocount on
declare @cols int,@s varchar(8000),@i varchar(10),@b varchar(8000)
select top 1
@cols=12, --一条记录最多12列
@s='',
@i=case when count(*)>@cols then @cols-1 else count(*)-1 end,
@b=',
[avg]=case when (a.id-b.id)/'+rtrim(@cols)+'=b.gid then cast(b.[avg] as varchar) else '''' end,
[max]=case when (a.id-b.id)/'+rtrim(@cols)+'=b.gid then cast(b.[max] as varchar) else '''' end,
[min]=case when (a.id-b.id)/'+rtrim(@cols)+'=b.gid then cast(b.[min] as varchar) else '''' end,
[极差]=case when (a.id-b.id)/'+rtrim(@cols)+'=b.gid then cast(b.[max]-b.[min] as varchar) else '''' end
from # a
left join(
select [desc],
[avg]=cast(avg(cast(data as numeric(5,1))) as decimal(5,1)),
[max]=cast(max(cast(data as numeric(5,1))) as decimal(5,1)),
[min]=cast(min(cast(data as numeric(5,1))) as decimal(5,1)),
gid=(max(id)-min(id))/'+rtrim(@cols)+',
id=min(id)
from #
group by [desc]
)b on a.[desc]=b.[desc]
-- and (a.id-b.id)/'+rtrim(@cols)+'=b.gid
group by a.[desc],(a.id-b.id)/'+rtrim(@cols)+',b.gid,b.[avg],b.[max],b.[min]'
from tb
group by [desc]
order by count(*) desc
while @i>=0
select @s=',[data'+rtrim(@i+1)+']=max(case (a.id-b.id)%'+rtrim(@cols)+' when '+@i+' then data else '''' end)'+@s,
@i=@i-1
exec('
select id=identity(int,1,1),[desc],dotime,data=cast(data as varchar)
into #
from tb
order by [desc]--,dotime select a.[desc]'+@s+@b)
go--执行查询
exec p_test
godrop proc p_test
drop table tb/*--输出结果
desc data1 data2 data3 data4 data5 data6 data7 data8 data9 data10 data11 data12 avg max min 极差
---------- ------ ----- ----- ----- ----- ----- ----- ----- ----- ------- ------ ------ ----- ----- ----- ----
PH值 2.0 3.6 5.0 6.0 2.0 3.6 5.0 6.0 2.0 3.6 5.0 6.0
PH值 2.0 3.6 5.0 66.0 666.0 46.6 666.0 2.0 664.0
浓度 2.3 2.5 2.6 2.1 2.4 2.6 2.1 0.5
--*/