--格式化报表
--下面是测试
--测试数据
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 表
