格式化报表.sql

来自「一个很好的sqlserver2000数据库函数包希望大家喜欢」· SQL 代码 · 共 103 行

--格式化报表

--下面是测试

--测试数据
create table 表(店名 varchar(4),电话 varchar(6),商品 varchar(2),数量 int,价格 int)
insert into 表 
select '店1','123456','Ax',1,30
union select '店2','223456','Az',3,35
union select '店3','223456','Ay',2,36
union select '店4','223456','Az',3,45
union select '店5','223456','Au',7,36
union select '店2','223456','Aa',3,35
union select '店3','223456','Ab',2,36
union select '店4','223456','Ac',3,45
union select '店5','223456','Au',7,36
union select '店2','223456','Ad',3,35
union select '店2','223456','Ae',2,36
union select '店2','223456','Af',3,45
union select '店2','223456','Ag',7,36
union select '店2','223456','Ah',3,35
union select '店2','223456','Ai',2,36
union select '店2','223456','Aj',3,45
union select '店2','223456','Ak',7,36
go

--实现查询的存储过程
create proc p_qry
@size int=6		--每行的商品数量
as
set nocount on
select 店名,gid=0,gid1=0
	,商品=cast(商品 as char(6))+space(4)
		+cast(数量 as char(6))+space(4)
		+cast(价格 as char(4))
into #t from 表 order by 店名
declare @dm varchar(4),@i int,@g int
update #t set @i=case when @dm=店名 and @i<@size then @i+1 else 1 end
		,@g=case when @dm=店名 then case @i when 1 then @g+1 else @g end else 1 end
	,gid=@i,gid1=@g,@dm=店名

declare @s1 varchar(8000),@s2 varchar(8000)
select @s1='',@s2=''
while @size>0
	select @s1=',['+cast(@size as varchar)+']=max(case gid when '
		+cast(@size as varchar)+' then 商品 else '''' end)'
		+@s1
		,@s2='+['+cast(@size as varchar)+']'+@s2
		,@size=@size-1
set @s2=substring(@s2,2,8000)
exec('
select re from(
	select 店名,id=1,re='+@s2+' from(
	select 店名'+@s1+' from #t group by 店名,gid1
	)a 
	union all
	select 店名,id=0
		,re=''店名：''+店名
		+space(4)+''电话：''+电话
		+space(4)+''总价：''+cast(总价 as varchar)
	from(
		select 店名,电话=max(电话),总价=sum(数量*价格) 
		from 表 group by 店名
	)a
	union all
	select 店名,id=3	,re=replicate(''-'',100)
	from 表 group by 店名
) a order by 店名,id
')
set nocount off
go

--调用:
exec p_qry 3
go

--删除测试环境
drop proc p_qry
drop table 表

/*--测试效果

re                                                                                                   
-------------------------------------------------------------------------
店名：店1    电话：123456    总价：30
Ax        1         30  
-------------------------------------------------------------------------
店名：店2    电话：223456    总价：1338
Aa        3         35  Ad        3         35  Ae        2         36  
Af        3         45  Ag        7         36  Ah        3         35  
Ai        2         36  Aj        3         45  Ak        7         36  
Az        3         35  
-------------------------------------------------------------------------
店名：店3    电话：223456    总价：144
Ab        2         36  Ay        2         36  
-------------------------------------------------------------------------
店名：店4    电话：223456    总价：270
Ac        3         45  Az        3         45  
-------------------------------------------------------------------------
店名：店5    电话：223456    总价：252
Au        7         36  
-------------------------------------------------------------------------
--*/