分组交叉表.sql

这是CSDN SQL Server 版主邹建的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.slsj<convert(char(10),@date2+1,120)
	and 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
--*/