blog.Toby

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  130 随笔 :: 2 文章 :: 150 评论 :: 0 Trackbacks

--各合作机构自年初以来每周报告数量

 

select  b.f0010 jg,datepart(ww,a.creat_tm) zc,count(*) cnt into #t

 

from RPT_D_BSC a,RPT_R_ORG b

 

where b.f0040='4' and a.creat_tm>'2007-01-01' and a.org_cl=b.org_cl

 

group by b.f0010,datepart(ww,a.creat_tm)

 

order by b.f0010,datepart(ww,a.creat_tm)

 

declare @sql varchar(8000)

 

set @sql = 'select jg as ' + '姓名'

 

select @sql = @sql + ', isnull(sum(case zc when ''' + cast(zc as varchar(2)) + ''' then cnt end),0) [' + cast(zc as varchar(2)) + ']'

from (select distinct zc from #t) as a

order by zc


set @sql = @sql + 'from #t group by jg order by jg'

--print @sql 

exec(@sql)

posted on 2007-08-14 14:53 渠上月 阅读(681) 评论(1)  编辑  收藏 所属分类: sql (sqlServer)

评论

# re: 数据库行转列的算法 2007-11-19 10:24 toby
CREATE TABLE tb(sn varchar(20),process_id int,data_id int,measuredata numeric(9,2))
INSERT tb SELECT '12770006 ',1,1,1.34
UNION ALL SELECT '12770006 ',1,2,046
UNION ALL SELECT '12770006 ',1,3,9.82
UNION ALL SELECT '14061916 ',2,1,5.5
UNION ALL SELECT '14061916 ',2,2,4.36
UNION ALL SELECT '12770006 ',2,1,6.43
UNION ALL SELECT '12770006 ',2,2,0.12
UNION ALL SELECT '12770006 ',2,3,6.37
UNION ALL SELECT '14061916 ',3,1,3.4
UNION ALL SELECT '14061916 ',3,2,1.1

--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s= 'SELECT sn,process_id '
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+ ', '+QUOTENAME(data_id)
+N '=SUM(CASE data_id WHEN '+QUOTENAME(data_id,N ' ' ' ')
+N ' THEN measuredata END) '
FROM tb
GROUP BY data_id

--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N '
FROM tb
GROUP BY sn,process_id ')  回复  更多评论
  


只有注册用户登录后才能发表评论。


网站导航: