62,041
社区成员
发帖
与我相关
我的任务
分享
create table cj --创建表cj
(
ID Int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1
Name Varchar(50),
Subject Varchar(50),
Result Int,
primary key (ID) --定义ID为表cj的主键
);
--Truncate table cj
--Select * from cj
Insert into cj
Select '张三','语文',80 union all
Select '张三','数学',90 union all
Select '张三','物理',85 union all
Select '李四','语文',85 union all
Select '李四','物理',82 union all
Select '李四','英语',90 union all
Select '李四','政治',70 union all
Select '王五','英语',90
--行列转换
Declare @sql varchar(8000)
Set @sql = 'Select Name as 姓名'
Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'
from (select distinct Subject from cj) as cj --把所有唯一的科目的名称都列举出来
Select @sql = @sql+' from cj group by name'
Exec (@sql)
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected System.Data.DataTable CreateMengxianhuiDataSource()
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataRow dr;
dt.Columns.Add(new System.Data.DataColumn("id", typeof(System.Int32)));
dt.Columns.Add(new System.Data.DataColumn("姓名", typeof(System.String)));
dt.Columns.Add(new System.Data.DataColumn("学号", typeof(System.String)));
dt.Columns.Add(new System.Data.DataColumn("性别", typeof(System.String)));
System.Random rd = new System.Random();
for (int i = 0; i < 10; i++)
{
dr = dt.NewRow();
dr[0] = i + i;
dr[1] = "孟子E章" + i.ToString();
dr[2] = "00" + (1 + i).ToString();
dr[3] = "男";
dt.Rows.Add(dr);
}
return dt;
}
protected void Page_Load(object sender, EventArgs e)
{
DataList1.DataSource = CreateMengxianhuiDataSource();
DataList1.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<script type="text/javascript">
window.onload = function () {
d = document.getElementById("<%=DataList1.ClientID %>");
row = d.rows.length;
if (row == 0) {
alert("表格没有数据,无需进行转换。");
return;
}
column = d.rows[0].cells.length;
html = new Array(column);
for (i = 0; i < column; i++) {
html[i] = new Array(row);
}
table = document.createElement("table");
tbody = document.createElement("tbody");
for (i = 0; i < d.rows.length; i++) {
for (j = 0; j < d.rows[i].cells.length; j++) {
html[j][i] = d.rows[i].cells[j].innerHTML;
}
}
for (i = 0; i < html.length; i++) {
tr = document.createElement("tr");
for (j = 0; j < html[i].length; j++) {
td = document.createElement("td");
td.innerHTML = html[i][j];
tr.appendChild(td);
}
tbody.appendChild(tr);
}
table.appendChild(tbody);
table.setAttribute("cellspacing", "0");
table.setAttribute("border", "1");
table.setAttribute("rules", "all");
table.setAttribute("style", "border-collapse:collapse;");
document.getElementById("x").innerHTML = "";
document.getElementById("x").appendChild(table);
}
</script>
</head>
<body>
<form id="form1" runat="server"><div id="x">
<asp:DataList ID="DataList1" runat="server">
<HeaderTemplate>
姓名:</td><td>
学号:
</td>
<td>
性别:
</HeaderTemplate>
<ItemTemplate>
<%#Eval("姓名")%></td>
<td>
<%#Eval("学号")%>
</td>
<td>
<%#Eval("性别")%>
</ItemTemplate>
</asp:DataList>
</div>
</form>
</body>
</html>