SQL交叉表实例.doc

SQL交叉表实例 2005-4-8 10:36:35 | By: 孤独的猎人 SQL交叉表实例 建表：在查询分析器里运行：CREATE TABLE Test (id int IDENTITY (1, 1) NOT NULL ,name nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL , subject nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ,Source numeric(18, 0) NULL ) ON PRIMARYGOINSERT INTO test (name,subject,Source) values (N张三,N语文,60)INSERT INTO test (name,subject,Source) values (N李四,N数学,70)INSERT INTO test (name,subject,Source) values (N王五,N英语,80)INSERT INTO test (name,subject,Source) values (N王五,N数学,75)INSERT INTO test (name,subject,Source) values (N王五,N语文,57)INSERT INTO test (name,subject,Source) values (N李四,N语文,80)INSERT INTO test (name,subject,Source) values (N张三,N英语,100)Go交叉表语句的实现：-用于：交叉表的列数是确定的select name,sum(case subject when 数学 then source else 0 end) as 数学,sum(case subject when 英语 then source else 0 end) as 英语,sum(case subject when 语文 then source else 0 end) as 语文 from test group by name-用于：交叉表的列数是不确定的declare sql varchar(8000)set sql = select name,select sql = sql + sum(case subject when +subject+ then source else 0 end) as +subject+,from (select distinct subject from test) as aselect sql = left(sql,len(sql)-1) + from test group by nameexec(sql)go一个通用的针对单表用的交叉表存储过程传入几个参数：TableName varchar(16) -表名纵轴 varchar(20) -交叉表最左面的列横轴 varchar(10) -交叉表最上面的列表体内容 numeric(10,2) -交叉表的数字内容是否加横向合计 bit -为1时在交叉表横向最右边加横向合计是否家纵向合计 bit -为1时在交叉表纵向最下边加纵向合计思路：1、取得disinct 横轴字段 后，取得唯一的横轴字段表2、根据横轴的唯一字段内容，循环整个表后动态生成一个Sql语句，像select 科室,sum(case(横轴字段表.).) from tablename group by 科室exec 生成的Sql3、根据参数是否合计，分别加合计字段，求出横向合计和纵向合计您看这个思路行吗？但有一个限制就是横轴不能太多，多了Sql可能会超过8000字符。一般不会这么多，如果太多就把横轴变为纵轴，总之取字段较少的做横轴，这个就是传参数时的问题了。如果弄成了，这个在一定的范围内应该是比较通用的了。对不！这是我的思路，具体写的时候，感觉到Sql的组合比较麻烦，能帮我写一下吗？=if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_qry) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_qryGO/*-生成交叉表的简单通用存储过程根据指定的表名,纵横字段,统计字段,自动生成交叉表并可根据需要生成纵横两个方向的合计注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段如果不要此功能,则去掉交换处理部分-邹建 204.06-*/*-调用示例exec p_qry syscolumns,id,colid,colid,name like s%,1,1-*/create proc p_qryTableName sysname, -表名纵轴 sysname, -交叉表最左面的列横轴 sysname, -交叉表最上面的列表体内容 sysname, -交叉表的数数据字段条件 varchar(1000),-查询的处理条件是否加横向合计 bit, -为1时在交叉表横向最右边加横向合计是否家纵向合计 bit -为1时在交叉表纵向最下边加纵向合计asdeclare s nvarchar(4000),sql varchar(8000)-规范条件set 条件=case when 条件 then where (+条件+) else end-判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段set s=declare a sysnameif(select case when count(distinct +纵轴+)from +TableName+ +条件+)=1select a=纵轴,纵轴=横轴,横轴=aexec sp_executesql s,N纵轴 sysname out,横轴 sysname out,纵轴 out,横轴 out-生成交叉表处理语句set s=set s=select s=s+,+cast(+横轴+ as varchar)+=sum(case +横轴+ when +cast(+横轴+ as varchar)+ then +表体内容+ else 0 end)from +TableName+条件+group by +横轴+exec sp_executesql s,Ns varchar(8000) out,sql out-是否生成合计字段的处理declare sum1 varchar(200),sum2 varchar(200),sum3 varchar(200)select sum1=case 是否加横向合计 when 1 then ,合计=sum(+表体内容+)else end,sum2=case 是否家纵向合计 when 1 then +纵轴+=case grouping(+纵轴+) when 1 then 合计 else cast(+纵轴