交叉表的简单实现1:使用存储过程

原创 2007年09月27日 17:07:00

有位朋友要求帮忙实现交叉表(将某些行变为列),之前虽然实现过,但没有整理,今天顺便整理一下,便于自己以后参考,希望对其他网友有帮助,欢迎指出不足之处^_^

数据库中原始数据类似:

要求前台显示为:


建立表结构:

CREATE TABLE [dbo].[t_Score] (
    
[ScoreId] [int] IDENTITY (11NOT NULL ,
    
[SubjectName] [varchar] (50NOT NULL ,
    
[StudentName] [varchar] (10NOT NULL ,
    
[ScoreValue] [real] NOT NULL ,
    
[ExamDate] [datetime] NOT NULL 
)

样本数据

INSERT INTO t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES('C语言''孙光'80'2006-01-05')

INSERT INTO t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES('日语''孙光'79'2006-07-06')

INSERT INTO t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES('C语言''孙光'89'2006-08-09')

INSERT INTO t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES('英语''王二'77'2006-09-10')

INSERT INTO t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES('英语''孙光'77'2006-07-06')

INSERT INTO t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES('C语言''王二'89'2006-08-09')


1。通过存储过程实现
实现要点:
a. 使用一个临时表(#ScoreTbl)存储指定学生的所有成绩(根据实际情况,可能有更多条件)
说明:这里无法使用表变量,因为表变量无法用于动态SQL语句中。

b.临时表#ScoreTbl中使用一个平均值标志位,因为将每个科目的平均值计算出来之后也存入了#ScoreTbl
当然,根据需求,还可以增加总成绩标志位等等

c.使用递归的SELECT语句创建动态 DateExam(考试时间)列(参考:Paul Nielsen 的 Microsoft SQL Server 2000宝典 P353 12-7 递归的Select变量)
注意:这里递归成的 @sql  语句有个潜在的bug,就是@sql长度必须小于8000个字符(如果使用sq_executeSql执行动态语句,必须声名为nvarchar,则只能使用4000个字符)
邹建大哥的 化解字符串不能超过8000的方法及交叉表的处理 讨论了三种可选择的方案。
本示例不做此讨论,只求实现交叉表^_^

d.使用CASE表达式选择 DateExam 对应的 ScoreValue(成绩值)(参考:Paul Nielsen 的 Microsoft SQL Server 2000宝典 P353 12-7 -2 动态交叉表查询)

e.使用聚合函数(这里使用SUM)包含ScoreValue列,因为ScoreValue未出现在GROUP BY子句中


具体实现:

ALTER PROC usp_GetCrossScore4(
@StuName varchar(10)
)
AS
-- creates a temp table to hold the score records
CREATE TABLE #ScoreTbl(
ScoreId 
int,
SubjectName 
varchar(50),
StudentName 
varchar(10),
ScoreValue 
real,
ExamDate 
datetime,
AvgFlag 
bit DEFAULT(0)    -- marks as the average of some subject
)
-- populates basic data of some student
INSERT INTO #ScoreTbl(ScoreId, SubjectName, StudentName, ScoreValue, ExamDate)
SELECT ScoreId, SubjectName, StudentName, ScoreValue, ExamDate FROM t_Score s WHERE s.StudentName = @StuName
-- calculates total for per subject and appends to the temp table
INSERT INTO #ScoreTbl(ScoreId, SubjectName, StudentName, ScoreValue, ExamDate, AvgFlag)
SELECT NULL, SubjectName, StudentName, AVG(ScoreValue), NULL1 FROM #ScoreTbl s GROUP BY SubjectName, StudentName
--SELECT * FROM #ScoreTbl

DECLARE @Sql varchar(8000)
-- NOTE: some known bug -> you make sure the length of the dynamical sql is less than 8000.
--
    fortunately, Mr Zou have made a deep discussion againt it at http://blog.csdn.net/zjcxc/archive/2003/12/29/20075.aspx.
SET @Sql = 'SELECT SubjectName 科目'
SELECT @sql = @sql + ', SUM(CASE ExamDate WHEN ''' + CONVERT(varchar(20), ExamDate, 102+ ''' THEN ScoreValue ELSE NULL END)''' + CAST(YEAR(ExamDate) AS VARCHAR+ '' + CAST(MONTH(ExamDate) AS VARCHAR+ '' + CAST(DAY(ExamDate) AS VARCHAR+ ''''
FROM (SELECT DISTINCT ExamDate FROM t_Score s WHERE s.StudentName = @StuName) ss
SET @Sql = @Sql + ', SUM(CASE AvgFlag WHEN 1 THEN ScoreValue ELSE NULL END) ''平均分'''
--PRINT @Sql
--
 runs the dynamical sql statement
EXEC(@sql+' FROM #ScoreTbl s GROUP BY SubjectName')
ALTER PROC usp_GetCrossScore4(
@StuName varchar(10)
)
AS
-- creates a temp table to hold the score records
CREATE TABLE #ScoreTbl(
ScoreId 
int,
SubjectName 
varchar(50),
StudentName 
varchar(10),
ScoreValue 
real,
ExamDate 
datetime,
AvgFlag 
bit DEFAULT(0)    -- marks as the average of some subject
)
-- populates basic data of some student
INSERT INTO #ScoreTbl(ScoreId, SubjectName, StudentName, ScoreValue, ExamDate)
SELECT ScoreId, SubjectName, StudentName, ScoreValue, ExamDate FROM t_Score s WHERE s.StudentName = @StuName
-- calculates total for per subject and appends to the temp table
INSERT INTO #ScoreTbl(ScoreId, SubjectName, StudentName, ScoreValue, ExamDate, AvgFlag)
SELECT NULL, SubjectName, StudentName, AVG(ScoreValue), NULL1 FROM #ScoreTbl s GROUP BY SubjectName, StudentName
--SELECT * FROM #ScoreTbl

DECLARE @Sql varchar(8000)
-- NOTE: some known bug -> you make sure the length of the dynamical sql is less than 8000.
--
    fortunately, Mr Zou have made a deep discussion againt it at http://blog.csdn.net/zjcxc/archive/2003/12/29/20075.aspx.
SET @Sql = 'SELECT SubjectName 科目'
SELECT @sql = @sql + ', SUM(CASE ExamDate WHEN ''' + CONVERT(varchar(20), ExamDate, 102+ ''' THEN ScoreValue ELSE NULL END)''' + CAST(YEAR(ExamDate) AS VARCHAR+ '' + CAST(MONTH(ExamDate) AS VARCHAR+ '' + CAST(DAY(ExamDate) AS VARCHAR+ ''''
FROM (SELECT DISTINCT ExamDate FROM t_Score s WHERE s.StudentName = @StuName) ss
SET @Sql = @Sql + ', SUM(CASE AvgFlag WHEN 1 THEN ScoreValue ELSE NULL END) ''平均分'''
--PRINT @Sql
--
 runs the dynamical sql statement
EXEC(@sql+' FROM #ScoreTbl s GROUP BY SubjectName')

测试代码:

效果:


源码:
 

交叉表 通用存储过程

CREATE   proc   p_qry     @TableName   sysname,   --表名     @纵轴   sysname,     --交叉表最左面的列     @横轴   s...
  • fanzhonglei
  • fanzhonglei
  • 2008年04月18日 17:37
  • 453

交叉统计报表存储过程的实现

用了2个小时写的下面的过程,供大家学习与交流,并且测试通过,不过代码写的很冗余.大家自己优化一下就OK了下面的存储过程是根据具体的要求来写的,你也可以根据自己的要求重新写,如果你对各种复杂的交叉报表有...
  • feier2004
  • feier2004
  • 2007年05月17日 17:08
  • 1050

SQL存储过程实现SPSS交叉表(有图有真相)

SP代码: /****** Object: StoredProcedure [dbo].[Pro_CrossTable] Script Date: 03/27/2014 20:46:38 ...
  • zhouhaiqing0905
  • zhouhaiqing0905
  • 2014年03月27日 20:51
  • 1485

交叉表的存储过程

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N[dbo].[p_qry])   and...
  • HenryWang99888
  • HenryWang99888
  • 2009年04月21日 21:48
  • 233

Sql交叉表简单实现

最近碰到一需求,是酱紫的: 一个销售商品表, 每年每月每天都卖出N种不同种类的商品, 最后要统计每月每种商品的销售额. 或每年的, 在此我模拟创建了一个简单的表, 主要说明经验和大家一块分享, 若发现...
  • taowei1987
  • taowei1987
  • 2009年07月30日 21:22
  • 298

利用Oracle存储过程实现中国式的交叉统计报表

很多时候,我们需要做一些中国式的报表,例如:我们有一下几张数据表来表示某大学,对各个省份的招生计划信息,省份信息表,专业信息表,招生计划信息表,那么我们想要统计一个交叉的报表,每个专业在各个省份的招生...
  • sandloves
  • sandloves
  • 2010年06月24日 16:03
  • 2181

SQL 交叉表存储过程

 SQL存储过程,主要产生交叉表,语句如下:ALTER        PROCEDURE accident_report   @areaCode varchar(20), --地区编码   @tota...
  • atd_nian
  • atd_nian
  • 2009年02月21日 16:39
  • 401

存储过程实现交叉表

物料编码 供应商ID 日期 单价 001 A011    2008.3.1 1.3 001 A011   2008.7.9 1.5 001 A011   2008.12.1 1.1 以上是不同日期同一...
  • ziren
  • ziren
  • 2009年03月14日 10:16
  • 583

oracle数据库报表取数---存储过程方式

Sql代码   --员工表   create table t_employee(   id number(10) primary key,   name varchar2(20),   age ...
  • Alsyuan
  • Alsyuan
  • 2017年06月14日 21:09
  • 372

javascript实现多表头分类交叉报表:html表格

DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> HTML> HEAD> HEAD> BODY> div id...
  • KimmKing
  • KimmKing
  • 2007年10月21日 23:03
  • 7628
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:交叉表的简单实现1:使用存储过程
举报原因:
原因补充:

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