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

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

数据库中原始数据类似：

要求前台显示为：


建立表结构：

CREATE   TABLE   [ dbo ] . [ t_Score ]  (
     [ ScoreId ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ SubjectName ]   [ varchar ]  ( 50 )  NOT   NULL  ,
     [ StudentName ]   [ varchar ]  ( 10 )  NOT   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),  NULL ,  1   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.youkuaiyun.com/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),  NULL ,  1   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.youkuaiyun.com/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 ' )

测试代码：

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script type="text/C#" runat="server">
    void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack) {
            LoadScoreData();   
        }
    }

    void drpStu_SelectedIndexChanged(object sender, EventArgs e)
    {        
        LoadScoreData();
    }

    void LoadScoreData()
    {
        string stuName = drpStu.SelectedValue;
        string connStr = "SERVER=.;DATABASE=DemoLib;UID=sa";
        string spName = "usp_GetCrossScore4";
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(spName, connStr);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.Add("@StuName", stuName);
        da.Fill(ds, "CrossScore");

        da.SelectCommand.CommandType = CommandType.Text;
        da.SelectCommand.CommandText = "SELECT SubjectName, StudentName, ScoreValue, ExamDate FROM t_Score WHERE StudentName = @StuName";
        da.Fill(ds, "FlatScore");
        
        grdCrossScore.DataSource = ds.Tables[0];
        grdCrossScore.DataBind();

        grdFlatScore.DataSource = ds.Tables[1];
        grdFlatScore.DataBind();
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Cross Table Demo</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    请选择：<asp:DropDownList ID="drpStu" OnSelectedIndexChanged="drpStu_SelectedIndexChanged" AutoPostBack="true" runat="server">
        <asp:ListItem Value="孙光">孙光</asp:ListItem>
        <asp:ListItem Value="王二">王二</asp:ListItem>
    </asp:DropDownList>
    <br />
    <b>Cross:</b>
    <asp:DataGrid ID="grdCrossScore" runat="server" BorderColor="SteelBlue" BorderWidth="1px" CellPadding="4" ForeColor="#333333" >
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <EditItemStyle BackColor="#2461BF" />
        <SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <AlternatingItemStyle BackColor="White" />
        <ItemStyle BackColor="#EFF3FB" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    </asp:DataGrid>    
    <br />
    <b>Flat:</b>
     <asp:DataGrid ID="grdFlatScore" runat="server" BorderColor="#336699" BorderWidth="1px" CellPadding="4" ForeColor="#333333" AutoGenerateColumns="False" >
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditItemStyle BackColor="#999999" />
        <SelectedItemStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <AlternatingItemStyle BackColor="White" ForeColor="#284775" />
        <ItemStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
         <Columns>
             <asp:BoundColumn DataField="SubjectName" HeaderText="科目"></asp:BoundColumn>
             <asp:BoundColumn DataField="ScoreValue" HeaderText="成绩"></asp:BoundColumn>
             <asp:BoundColumn DataField="ExamDate" DataFormatString="{0:yyyy年M月dd日}" HeaderText="考试时间"></asp:BoundColumn>             
         </Columns>
    </asp:DataGrid> 
    <asp:GridView ID="GridView1" runat="server">
        <Columns>
        <asp:BoundField HtmlEncode="false" ApplyFormatInEditMode="true" DataField="ExamDate" DataFormatString="{0:dd/MM/yyyy}" HeaderText="ExamDate" />
        </Columns>                
    </asp:GridView>
    </div>
    </form>
</body>
</html>

效果：


源码：