本文介绍了一种基于SQL的动态交叉表构建方法，适用于多种数据库环境。通过动态SQL生成及C#编程实现，解决了传统静态交叉表灵活性不足的问题。
本例所采用的交叉表的实现方式是建立在SQL的基础上，所以可能会由于使用的数据库不一致需要修改SQL，本例采用的是oracle9i，多重表头实现控件为C1FlexGrid。 首先看一个交叉表的SQL：
交叉表效果 本SQL是将一个学生成绩按课目，期中、期末进行统计，就能够实现以人名为汇总的按课目、期数的交叉表，在这个交叉表中会有一个缺陷 1）可能由于课目并非固定而此sql就需要动态构建 2）可能并非所有课目都会有期中、期末成绩，那么就需要只对有期中或期末成绩的课目按期数做汇总 为此，需要通过动态构造交叉表，可考虑使用存储过程或程序构造sql，本人用C#写了一个通用的构造sql函数，但目前只支持3层交叉表头
select name,
sum(case when km='英语' and type = '1' then cj else 0 end) as 英语_1,
sum(case when km='英语' and type = '2' then cj else 0 end) as 英语_1,
sum(case when km='数学' and type = '1' then cj else 0 end) as 数学_1,
sum(case when km='数学' and type = '2' then cj else 0 end) as 数学_2,
sum(case when km='语文' and type = '1' then cj else 0 end) as 语文_1,
sum(case when km='语文' and type = '2' then cj else 0 end) as 语文_2
from test
group by name;
数据表结构如：name km type cj
| 姓名 | 语文 | 数学 | ||
| 其中 | 期末 | 期中 | 期末 | |
| 张 | 12 | 2 | 44 | 55 |
| 李 | 24 | 2 | 55 | 5 |
/// <summary>
/// 购造交叉表，最多支持3层（如果递归将无限层，但sql长度有限制），只支持一列汇总列,增加了对数据的过滤
/// 如果添加汇总列无数据则不加入结果列中
/// </summary>
/// <param name="stablename">基础表名</param>
/// <param name="spricols">group列，主列</param>
/// <param name="scrosscols">需要交叉的列</param>
/// <param name="sfilter">基础表过滤条件</param>
/// <param name="sumcol">汇总的数据列</param>
/// <returns></returns>
private string GetCrossSQL(string stablename, string spricols, string scrosscols, string sfilter, string sumcol)
{
string strSQL = "";
//第一步先获取需要交叉的字段的列表
string[] s = scrosscols.Split(',');
ArrayList al_cols = new ArrayList();
for (int i = 0; i < s.Length; i++)
{
string s_temp = "";
s_temp = "select distinct " + s[i] + " from " + stablename + sfilter;
DataTable dt_temp = this.dac.ExecuteDataTable(s_temp);
al_cols.Add(dt_temp);
}
//第二步，构造sum语句
string sumsql = "";
switch (s.Length)
{
case 1:
foreach (DataRow row0 in (al_cols[0] as DataTable).Rows)
{
string s_temp = " sum( case when " + s[0] + "='" + row0[0].ToString() + "' then " + sumcol + " else 0 end ) as " + s[0].Substring(0,2)+"_"+row0[0].ToString().Trim();
if (IsExistCol(stablename, spricols, sfilter, s_temp))
{
sumsql += "," + s_temp;
}
}
break;
case 2:
foreach (DataRow row0 in (al_cols[0] as DataTable).Rows)
{
foreach (DataRow row1 in (al_cols[1] as DataTable).Rows)
{
string s_temp = "sum( case when " + s[0] + "='" + row0[0].ToString() + "' and " + s[1] + "= '" + row1[0].ToString() + "' then " + sumcol + " else 0 end ) as " + s[0].Substring(0,2)+"_"+row0[0].ToString().Trim() + "#" +s[1].Substring(0,2)+"_"+ row1[0].ToString().Trim();
if (IsExistCol(stablename, spricols, sfilter, s_temp))
{
sumsql += "," + s_temp;
}
}
}
break;
case 3:
foreach (DataRow row0 in (al_cols[0] as DataTable).Rows)
{
foreach (DataRow row1 in (al_cols[1] as DataTable).Rows)
{
foreach (DataRow row2 in (al_cols[2] as DataTable).Rows)
{
//sumsql += ",sum( case when " + s[0] + "='" + row0[0].ToString() + "' and " + s[1] + "= '" + row1[0].ToString() + "' and " + s[2] + "='" + row2.ToString() + "' then " + sumcol + " else 0 end ) as " + s[0].Trim() + "#" + row0[0].ToString().Trim() + "_" + s[1].Trim() + "#" + row1[0].ToString().Trim() + "_" + s[2].Trim() + "#" + row2[0].ToString().Trim();
string s_temp = "sum( case when " + s[0] + "='" + row0[0].ToString() + "' and " + s[1] + "= '" + row1[0].ToString() + "' and " + s[2] + "='" + row2[0].ToString() + "' then " + sumcol + " else 0 end ) as "+ s[0].Substring(0,2)+"_"+row0[0].ToString().Trim() + "#" +s[1].Substring(0,2)+"_"+ row1[0].ToString().Trim() + "#" +s[2].Substring(0,2)+"_"+ row2[0].ToString().Trim();
if (IsExistCol(stablename, spricols, sfilter, s_temp))
{
sumsql += "," + s_temp;
}
}
}
}
break;
}
//拼接sql
strSQL = " select " + spricols + sumsql + " from " + stablename + " " + sfilter + " group by " + spricols;
return strSQL;
}
private bool IsExistCol(string stablename, string spricols, string sfilter, string sumsql)
{
string strSQL = " select " + sumsql + " from " + stablename + " " + sfilter ;
try
{
if (Convert.ToDecimal(this.dac.ExecuteScalar(CommandType.Text, strSQL).ToString()) != 0)
return true;
else
return false;
}
catch (System.Exception se)
{
return false;
}
}public void CreateGridTitle(C1.Win.C1FlexGrid.C1FlexGrid grid, DataTable dt_data)
{
grid.AllowAddNew = true;
//第一步获取所有的数据表头，并获取最大表头行数
grid.AllowMerging = C1.Win.C1FlexGrid.AllowMergingEnum.FixedOnly;
ArrayList al_title = new ArrayList();
int fixrow = 0;
for (int i = 0; i < dt_data.Columns.Count; i++)
{
string[] s = dt_data.Columns[i].Caption.Split('#');
al_title.Add(s);
if (s.Length > fixrow)
fixrow = s.Length;
}
//插入表头
grid.Rows.Fixed = fixrow;
for (int i = 0; i < grid.Cols.Count; i++)
{
grid.Cols[i].AllowMerging = true;
}
for (int i = 1; i <= dt_data.Columns.Count; i++)
{
for (int j = 0; j < fixrow; j++)
{
if (grid.Rows.Count < fixrow)
grid.Rows.Add();
if ((al_title[i - 1] as string[]).Length <= j)
grid[j, i] = (al_title[i - 1] as string[])[(al_title[i - 1] as string[]).Length - 1].Trim();
else
grid[j, i] = (al_title[i - 1] as string[])[j].Trim();
grid.Rows[j].AllowMerging = true;
}
}
grid.Styles.Fixed.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.CenterCenter;
}
基本过程就如以上示例，根据实际情况可以对以上方法再做改进，比如可以设计一个动态配置交叉表工具。
由于太忙，没有功夫去自己研究，该方法也就提供入门指引，待日后再做细化了。
![]()
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
