用存储过程实现交叉报表(动态列)

由于Birt不支持交叉报表(虽然能勉强实现，但十分麻烦)，因此这几天郁闷死我了！最后决定在数据库里用存储过程先生成交叉表，然后直接用JSP显示出来。系统虽然是用的struts+spring+hibernate架构的，但为了简单起见就偷懒了！

记录下这些内容方便自己日后查看，出现一次意外就是把数据库还原回来，结果存储过程全没了白忙一场，还好印象比较深又搞定了！

由于我是好几张表关联，大概有6张吧，哈哈，所以先建个视图trainView把6张表全联接起来，代码：[code]SELECT tb.id, tb.sex, tb.age, tb.trainDays, tb.trainYear, p.PoliceNum, p.Name, p.deptname,
p.GradationNum, tm.trainMark, ti.name AS itemName, ti.code, ti.parentId
FROM dbo.TrainBase tb INNER JOIN
(SELECT p.*, d .deptname
FROM Misperson_Misdept pd LEFT JOIN
misperson p ON pd.misperson_id = p.id LEFT JOIN
misdept d ON pd.misdept_id = d .id) p ON
tb.personId = p.ID LEFT OUTER JOIN
dbo.TrainMark tm ON tb.id = tm.trainId INNER JOIN
dbo.TrainItem ti ON tm.itemId = ti.id[/code]

各张表的表结构在此就不给出了！

然后是存储过程trainReport的代码：[code]CREATE PROCEDURE trainReport
@trainYear varchar(20)
AS
declare @sql varchar(8000)

set @sql = 'select name as 姓名,sex as 性别,age as 年龄,policeNum as 警号,deptName as 所在单位,trainDays as 参加集中训练时间,'

select @sql = @sql + 'sum(case itemName when '''+itemName+''' then trainMark else 0 end) as '''+itemName+''','
from (select distinct itemName from trainView) as a
select @sql = left(@sql,len(@sql)-1) + ',trainYear,GradationNum from trainView where trainYear='+@trainYear+' group by name,sex,age,policeNum,deptName
,trainDays,trainYear,GradationNum order by GradationNum'
exec(@sql)
GO[/code]

代码都没有经过优化，以实现为第一考虑！
显示部分就直接在JSP里用JDBC调用存储过程，然后显示列和行
部分代码如下：[code]<%
String trainYear = request.getParameter("trainYear");
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver")
.newInstance();
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=YJGA";
String user = "sa";
String password = "sa";
Connection conn = DriverManager.getConnection(url, user, password);
CallableStatement proc = conn
.prepareCall("execute trainReport '" + trainYear + "'");
ResultSet rs = proc.executeQuery();
ResultSetMetaData dm = rs.getMetaData();
%>[/code]
动态显示列名：[code]<%
for(int i = 1; i <= dm.getColumnCount()-2; i++){
if(i==6){
out.print("<td class=xl width=200>" + dm.getColumnName(i) + "</td>");
}else{
out.print("<td class=xl width=100>" + dm.getColumnName(i) + "</td>");
}
}
%>[/code]

然后是显示记录：[code]<%
while(rs.next()){
out.print("<tr height=25 style='mso-height-source:userset;height:23.25pt'>");
for(int i = 1; i <= dm.getColumnCount()-2; i++){
out.print("<td class=xl>" + rs.getString(i) + "</td>");
}
out.print("</tr>");
}
%> [/code]

最有用的部分就是存储过程trainReport,实现了将动态的行转为列的功能!
最终结果如下，参加集中训练时间后面那些列是根据每年的训练项目动态变化的！

姓名　　性别　年龄　警号　　所在单位　参加集中训练时间　测试项目　其它项目　现场急救
陈龙　　男　　25　　990151　计通科　　15　　　　　　　90　　　　50　　　　0
胡建欧　男　　25　　990150　计通科　　15　　　　　　　0　　　　　90　　　　80