有位朋友要求帮忙实现交叉表(将某些行变为列),之前虽然实现过,但没有整理,今天顺便整理一下,便于自己以后参考,希望对其他网友有帮助,欢迎指出不足之处^_^
数据库中原始数据类似:
![fs.GIF](https://i-blog.csdnimg.cn/blog_migrate/cd945b6f93bf2fddc9ab717df68ffaa1.gif)
要求前台显示为:
![cs.GIF](https://i-blog.csdnimg.cn/blog_migrate/1eba34572c696b85bf31da1d3f7168e9.gif)
建立表结构:
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
'
)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT
INTO
t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES
(
'
日语
'
,
'
孙光
'
,
79
,
'
2006-07-06
'
)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT
INTO
t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES
(
'
C语言
'
,
'
孙光
'
,
89
,
'
2006-08-09
'
)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT
INTO
t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES
(
'
英语
'
,
'
王二
'
,
77
,
'
2006-09-10
'
)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT
INTO
t_Score(SubjectName, StudentName, ScoreValue, ExamDate)
VALUES
(
'
英语
'
,
'
孙光
'
,
77
,
'
2006-07-06
'
)
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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.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),
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.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
'
)
测试代码:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
<script type="text/C#" runat="server">![dot.gif](https://www.cnblogs.com/Images/dot.gif)
void Page_Load(object sender, EventArgs e)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (!IsPostBack)
{
LoadScoreData();
}
}
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
void drpStu_SelectedIndexChanged(object sender, EventArgs e)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
LoadScoreData();
}
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
void LoadScoreData()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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");
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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();
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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>
效果:
![](https://i-blog.csdnimg.cn/blog_migrate/b2d49a8023077b49661ca3c9ff3c61b7.jpeg)
源码:
下载
下篇介绍如何使用程序动态构造交叉表。
交叉表的简单实现2:使用前端程序实现