sql 分组交叉表

CREATE proc p_qry
@JFKMBID varchar(20),
@JFKMBSYSID varchar(1000),
@date1 datetime,
@date2 datetime,
@r_count int
as
set nocount on

--分拆 @JFKMBSYSID
declare @i int
set @i=len(@JFKMBSYSID)
if isnull(@i,0)<1 return
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount @i
select jFKMBSYSID=substring(@JFKMBSYSID,id,patindex('%[,;]%',stuff(@JFKMBSYSID+',',1,id,'')))
,gid=case when substring(','+@JFKMBSYSID,id,1)=',' then 0 else 1 end,sid=0
into #t1 from #t
where substring(','+@JFKMBSYSID,id,1) in(',',';')
order by id

--生成分组标志
select @i=1
update #t1 set @i=case when gid=0 then @i else @i+1 end,gid=@i

--生成处理数据
select a1.gid,a2.PX,b.qydm,cnt=count(*),sid=0
into #t2
from #t1 a1,S_IntegralMode a2,S_PntegralFactInfo b
where a1.jFKMBSYSID=a2.jFKMBSYSID
and a2.jFKMBSYSID=b.jFKMBSYSID
and b.slsj>=convert(char(10),@date1,120)
and b.slsjand b.JFKMBID=@JFKMBID
group by a1.gid,a2.PX,b.qydm
order by a1.gid,a2.PX,b.qydm

--去掉不符合条件的记录
delete a from #t2 a
where not exists(
select * from #t2
where qydm=a.qydm and gid=a.gid and cnt>=@r_count)

--生成交叉表处理标志
declare @gid int,@PX int
set @i=1
update #t2 set @i=case when @gid=gid
then case when @PX=PX then @i else @i+1 end
else 1 end
,@gid=gid,@PX=PX,sid=@i

--生成交叉表处理语句
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000)
select @s1='',@s2='',@s3='',@s4=''
,@i=max(sid) from #t2
while @i>0
select @s1=',[col'+rtrim(@i)
+']=cast(sum(case sid when '+rtrim(@i)
+' then cnt else 0 end) as varchar)'+@s1
,@s2=',[col'+rtrim(@i)
+']=max(case sid when '+rtrim(@i)
+' then cast(PX as varchar) else '''' end)'+@s2
,@s3=',a.[col'+rtrim(@i)+']'+@s3
,@i=@i-1

--显示结果
exec('
select qymc=isnull(b.qymc,a.qydm)'+@s3+'
from(
select gid,qydm,s1=1'+@s1+' from #t2 group by gid,qydm
union all
select gid,''查询分组''+rtrim(gid),s1=0'+@s2+' from #t2 group by gid
union all
select gid,''分组''+rtrim(gid)+''合计'',s1=2'+@s1+' from #t2 group by gid
)a left join S_Enterprise b on a.qydm=b.qydm
order by a.gid,a.s1,a.qydm
')
GO

exec p_qry '4-2005','45,46,47,48;49,50,51,52,53;54,55;56,57,58;59','2005-1-1','2005-1-31',1
go

drop proc p_qry

/*--测试结果

qymc
------
查询分组1 1 2 3 4
象山医药药材有限公司医药商店 1 1 1 1 0
象山同仁堂大药房药业有限公司 3 3 3 3 0
分组1合计 4 4 4 4 0
查询分组2 5 6 7 8 9
象山医药药材有限公司医药商店 1 1 1 1 1
象山同仁堂大药房药业有限公司 3 3 3 3 3
分组2合计 4 4 4 4 4
查询分组3 10 11
象山医药药材有限公司医药商店 1 1 0 0 0
象山同仁堂大药房药业有限公司 3 3 0 0 0
分组3合计 4 4 0 0 0
查询分组4 12 13 14
象山医药药材有限公司医药商店 1 1 1 0 0
象山同仁堂大药房

药业有限公司 3 3 3 0 0
分组4合计 4 4 4 0 0
查询分组5 15
象山医药药材有限公司医药商店 1 0 0 0 0
象山同仁堂大药房药业有限公司 3 0 0 0 0
分组5合计 4 0 0 0 0
--*/