交叉表查询(TRANSFORM)

转载 2006年06月13日 16:40:00

sql中没有类似ACCESS的标准交叉表处理功能.   
  使用CASE和GROUP子句才是正确的选择(sql联机帮助也是这样说的,官方推荐)
动态列的问题,可以通过动态sql语句实现.

--参考:  
   
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_qry]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
  drop   procedure   [dbo].[p_qry]  
  GO  
   
  /*--生成交叉表的简单通用存储过程  
   
  根据指定的表名,纵横字段,统计字段,自动生成交叉表  
  并可根据需要生成纵横两个方向的合计  
   
  注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段  
  如果不要此功能,则去掉交换处理部分  
   
  --邹建   2004.06(引用请保留此信息)--*/  
   
  /*--调用示例  
   
  exec   p_qry   'syscolumns','id','colid','colid','name   like   ''s%''',1,1  
  --*/  
   
  create   proc   p_qry  
  @TableName   sysname, --表名  
  @纵轴   sysname, --交叉表最左面的列  
  @横轴   sysname, --交叉表最上面的列  
  @表体内容   sysname, --交叉表的数数据字段  
  @条件   varchar(1000),--查询的处理条件  
  @是否加横向合计   bit, --为1时在交叉表横向最右边加横向合计  
  @是否家纵向合计   bit --为1时在交叉表纵向最下边加纵向合计  
  as  
  declare   @s   nvarchar(4000),@sql   varchar(8000)  
   
  --规范条件  
  set   @条件=case   when   @条件<>''   then   '   where   ('+@条件+')'   else   ''   end  
   
  --判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段  
  set   @s='declare   @a   sysname  
  if(select   case   when   count(distinct   ['+@纵轴+'])<count(distinct   ['+@横轴+'])   then   1   else   0   end  
  from   ['+@TableName+']   '+@条件+')=1  
  select   @a=@纵轴,@纵轴=@横轴,@横轴=@a'  
  exec   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  
  ,N'@s   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(['  
  +@纵轴+']   as   varchar)   end'  
  else   '['+@纵轴+']'   end  
  ,@sum3=case   @是否家纵向合计  
  when   1   then   '   with   rollup'  
  else   ''   end  
   
  --生成交叉表  
  exec('select   '+@sum2+@sql+@sum1+'  
  from   ['+@TableName+']  
  '+@条件+'  
  group   by   ['+@纵轴+']'+@sum3)  
  go  

CSDN中相关链接:

http://topic.csdn.net/t/20041229/10/3684609.html

http://topic.csdn.net/t/20050510/01/3993699.html

http://community.csdn.net/Expert/topic/4815/4815049.xml?temp=.2712061

http://topic.csdn.net/t/20050528/09/4041580.html

交叉表、行列转换和交叉查询经典

交叉表、行列转换和交叉查询经典 一、什么是交叉表 “交叉表”对象是一个网格,用来根据...
  • cngkqy
  • cngkqy
  • 2007年12月05日 16:01
  • 16126

oracle 动态交叉查询 行列互换 oracle动态交叉表

问题描述如下: 有张表cityorder,有如下几个列:id,amount,city,date。表中有以下一些记录: id amount city date 1 12 001 08-11-1...
  • heicm
  • heicm
  • 2011年03月17日 15:36
  • 4466

Mysql5 实现交叉表查询

交叉表、行列转换和交叉查询经典 一、什么是交叉表 “交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成:     行...
  • a437629292
  • a437629292
  • 2015年12月15日 17:04
  • 1831

sqlserver一个动态交叉表的范例

社区问的人太多了,保存一个备用--建立测试环境set nocount oncreate table test(model varchar(20),date int ,qty int)insert in...
  • jinjazz
  • jinjazz
  • 2007年12月11日 09:55
  • 2639

sqlserver(相关子查询,动态交叉表篇)

 最近重新又翻看了一下关于sqlserver的书籍,主要查看了一下关于sql中的相关子查询和交叉表方面的知识。相关子查询和普通子查询区别在于:相关子查询引用了外部查询的列。这种引用外部查询的能力意味着...
  • cngkqy
  • cngkqy
  • 2007年12月05日 16:05
  • 2680

sql 交叉表查询

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[Rpt_ProductPrice] @IDEPAR...
  • shilang999
  • shilang999
  • 2014年03月01日 11:10
  • 621

交叉表查询中的多列显示

利用交叉表,我们可以对某个列字段的项目进行显示,比如[学科]来显示[考试成绩],那么是否能同时显示其[作业成绩]在同一个交叉表查询中?比如下面数据Table3+-----+--------+-----...
  • ACMAIN_CHM
  • ACMAIN_CHM
  • 2010年01月10日 22:51
  • 4376

oracle 交叉表

在oracle的test表中插入记录INSERT INTO test values (N张三,N语文,60)INSERT INTO test values (N李四,N数学,70)INSERT INT...
  • cut007
  • cut007
  • 2007年04月04日 20:57
  • 1262

Excel VBA利用Transform函数+SQL交叉汇总数据

一、VBA中transform函数基本语法:Creates a crosstab query. Syntax TRANSFORM aggfunction selectstatement PIVOT p...
  • gtxing
  • gtxing
  • 2016年10月26日 19:59
  • 647

SqlServer生成交叉表大全

 SqlServer如何生成动态交叉表查询VB+MS SqlServer,是我们目前开发数据库应用系统最常用的模式,翻翻以前的老帖子,有一些SqlServer的问题经常被提出来,但正确解答甚少,现把我...
  • cngkqy
  • cngkqy
  • 2007年12月05日 16:39
  • 12360
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:交叉表查询(TRANSFORM)
举报原因:
原因补充:

(最多只允许输入30个字)