利用前台程序控制行转列数据的输出,可能从效率和扩展性上更好.一个例子

采用   sql2k   +   .net2005   +   xslt完成.

效率情况分析:
若表中数据量比较大,   而统计出的结果数据比较少,那么效果比较好.
因为,数据库得出统计数据,   在少量的数据下由xslt完成格式输出工作.

当然,xslt完成的输出,特别是输出岗位数据这一步分,还是比较占web服务器资源的.   如果想省掉,那也很简单,   还是一句话,直接把数据以xml格式输出,用js来完成table的布局,让它去占访客的ie资源吧.

 

create TABLE pm(id varchar(20),name varchar(20),DeptName varchar(20),DeptID varchar(20),DeptSequence int,tnum int,Extension varchar(20))
go
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(1, '张三', '1部',1,10,1,'123')
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(2, '李四', '1部',1,10,2,'456')
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(3, '王五', '2部',2,20,1,'321')
go
create TABLE RI(id varchar(20),perid varchar(20),StateName varchar(20),partid varchar(20),RegisterDate datetime)
go
insert into RI(id,perid,StateName,partid,RegisterDate)values(1, 1, '面试通过',1,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 2, 1, '面试不通过',3,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 3, 2, '很黄很暴力',4,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 4, 3, '面试不通过',4,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 5, 1, '放弃',1,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 6, 2, '面试不通过',5,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 7, 3, '面试通过',6,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 8, 1, '好',7,getdate())
go

create TABLE part(id varchar(20),name varchar(20))
go
insert into part(id,name)values( 1, '文员')
insert into part(id,name)values( 2, '法师')
insert into part(id,name)values( 3, '牧师')
insert into part(id,name)values( 4, '盗贼')
insert into part(id,name)values( 5, '骑士')
insert into part(id,name)values( 6, '萨满')
insert into part(id,name)values( 7, '战士')
insert into part(id,name)values( 8, '德鲁伊')
insert into part(id,name)values( 9, '猎人')
insert into part(id,name)values( 10, '术士')
go




alter proc getDemoData
AS
begin
select a.DeptName,a.DeptSequence,a.Name,a.tnum,a.Extension,a.id perid,
        sum(case statename when '面试通过' then 1 else 0 end) passNum,
        count(*) viewNum
    from pm a
inner join ri b
    on a.id=b.perid
    group by a.DeptName,a.DeptSequence,a.Name,a.tnum,a.Extension,a.id 
    for xml raw



select c.name name,isnull(b.perid,0) perid,isnull(count(b.perid),0) perNum
    from part c
left join ri b
    on b.partid=c.id
    group by b.perid,c.name
    for xml raw
end
go


go
--drop table pm,ri,part
--go

 

demoXsltReport.aspx.cs

 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Xml.Xsl;
using System.Text;
using System.Data.SqlClient;

public partial class demoXsltReport : System.Web.UI.Page
...{
    protected void Page_Load(object sender, EventArgs e)
    ...{
        Response.Write(TransForm(buildXML(), "demoTransformData.xsl"));
    }

    private string getConnStr()
    ...{
        return ConfigurationManager.AppSettings["connstr"];
    }

    private XmlDocument buildXML()
    ...{
        StringBuilder sb = new StringBuilder();
        sb.Append("<?xml version="1.0" encoding="utf-8"?>");
        sb.Append("<root>");

        SqlConnection cn = new SqlConnection(getConnStr());
        cn.Open();
        SqlCommand cmd = new SqlCommand("getDemoData", cn);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read())
        ...{
            sb.Append(" <DeptData>" + dr[0].ToString() + "</DeptData>");
        }

        dr.NextResult();

        if (dr.Read())
        ...{
            sb.Append(" <PertData>" + dr[0].ToString() + "</PertData>");
        }
        dr.Close();
        cn.Close();
        cn.Dispose();

        sb.Append("</root>");

        XmlDocument xd = new XmlDocument();
        xd.LoadXml(sb.ToString());
        return xd;

    }

    public string TransForm(XmlDocument xd, string xslURL)
    ...{
        XslTransform xsl = new XslTransform();
        xsl.Load(Server.MapPath(xslURL));
        System.IO.MemoryStream t = new System.IO.MemoryStream();
        xsl.Transform(xd, null, t, null);
        return System.Text.UTF8Encoding.UTF8.GetString(t.ToArray());
    }
}

dataTransform.xsl

 

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.w3.org/1999/xhtml">
  <xsl:key name="pert_group" match="//PertData/row[@perid!=0]" use="@name" />
  <xsl:variable name="pertNum" select="count(//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1])"/>
  <xsl:template match="/">
    <html xmlns="http://www.w3.org/1999/xhtml">
      <head>
        <title>demo</title>
      </head>


      <body>
        <table border="1">
          <!--制作表头-->
          <tr>
            <td>DeptName</td>
            <td>DeptSequence</td>
            <td>PersonName</td>
            <td>tNum</td>
            <td>Extension</td>
            <td>perid</td>
            <td>PassNum</td>
            <td>viewNum</td>
            <!--
            <xsl:for-each select="//PertData/row[@perid!=0]">
              <td>
                <xsl:value-of select="@name"/>
              </td>
            </xsl:for-each>
            -->
            <xsl:call-template name="getPertNameList"/>
          </tr>
          <!--输出数据-->
          <xsl:for-each select="//DeptData/row">
            <tr>
              <td>
                <xsl:value-of select="@DeptName"/>
              </td>
              <td>
                <xsl:value-of select="@DeptSequence"/>
              </td>
              <td>
                <xsl:value-of select="@Name"/>
              </td>
              <td>
                <xsl:value-of select="@tnum"/>
              </td>
              <td>
                <xsl:value-of select="@Extension" disable-output-escaping="yes"/>
              </td>
              <td>
                <xsl:value-of select="@perid"/>
              </td>
              <td>
                <xsl:value-of select="@passNum"/>
              </td>
              <td>
                <xsl:value-of select="@viewNum"/>
              </td>

              <xsl:call-template name="getPertNumForDept">
                <xsl:with-param name="perid" select="@perid"/>
              </xsl:call-template>

            </tr>
          </xsl:for-each>
        </table>
      </body>
    </html>
  </xsl:template>



  <xsl:template name="getPertNumForDept">
    <xsl:param name="perid"/>
    <xsl:for-each select="//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1]">
      <xsl:sort data-type="text" select="@name"/>
      <td>
        <xsl:choose>
          <xsl:when test="@perid=$perid">
            <xsl:value-of select="@perNum"/>
          </xsl:when>
          <xsl:otherwise>0</xsl:otherwise>
        </xsl:choose>
      </td>
    </xsl:for-each>
    
  </xsl:template>

  <xsl:template name="getPertNameList">
    <xsl:for-each select="//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1]">
      <xsl:sort data-type="text" select="@name"/>
      <td>
        <xsl:value-of select="./@name"/>
      </td>
    </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>