DataTable行转列

来源：互联网发布：网络大电影预算编辑：程序博客网时间：2024/06/06 01:09

数据库中查询后的结果为：
col1 col2 col3 col4
AAAA AA 扬州 10.50
AAAA AA 北京 150.00
AAAA BB 南京 20.00
BBBB QQ 北京 50.00
BBBB PP 上海 45.00
BBBB PP 南京 100.00

怎么才能转换成下面这种格式：
扬州南京北京上海
AAAA AA 10.50 150.00
AAAA BB 20.00
BBBB QQ 50.00
BBBB PP 100.00 45.00

方法一：
DataTable   rs1=GetTable( "Select   col1,col2   From   TabName   Group   By   Col1,Col2   ");
DataTable   rs2=GetTable( "Select   distinct   Col3   From   TabName ");
for(int   i=0;i <rs2.Rows.Count;i++)
{
rs1.Columns.Add(rs2.Rows[i].ToString());
}
DataTable   rs3=GetTable( "Select   col1,col2,   col3,col4   From   TabName ");
for(int   i=0;i <rs3.Rows.Count;i++)
{
for(int   j=0;j <rs1.Rows.Count;j++)
{
if(rs1.Rows[j][ "col1 "].ToString()==rs3.Rows[i][ "col1 "].ToString()   &&   rs1.Rows[j][ "col2 "].ToString()==rs3.Rows[i][ "col2 "].ToString())
{
rs1.Rows[j][rs3.Rows[i][ "col3 "].ToString()]=rs3.Rows[i][ "col4 "].ToString();
break;
}
}
}

方法二：

单纯用SQL可能不行

DataTable   rs1=GetTable( "Select   col1,col2   From   TabName   Group   By   Col1,Col2   ");
DataTable   rs2=GetTable( "Select   distinct   Col3   From   TabName ");
for(int   i=0;i <rs2.Rows.Count;i++)
{
rs1.Columns.Add(rs2.Rows[i].ToString());
}
DataTable   rs3=GetTable( "Select   col1,col2,   col3,col4   From   TabName ");
for(int   i=0;i <rs3.Rows.Count;i++)
{
for(int   j=0;j <rs1.Rows.Count;j++)
{
if(rs1.Rows[j][ "col1 "].ToString()==rs3.Rows[i][ "col1 "].ToString()   &&   rs1.Rows[j][ "col2 "].ToString()==rs3.Rows[i][ "col2 "].ToString())
{
rs1.Rows[j][rs3.Rows[i][ "col3 "].ToString()]=rs3.Rows[i][ "col4 "].ToString();
break;
}
}
}

/// <summary>
        /// 执行DataTable中的查询返回新的DataTable
        /// </summary>
        /// <param name="dt">源数据DataTable</param>
        /// <param name="condition">查询条件</param>
        /// <returns></returns>
        private DataTable GetNewDataTable(DataTable dt,string condition)
        {
            DataTable newdt = new DataTable();
            newdt=dt.Clone();
            DataRow[] dr = dt.Select(condition);
            for(int i=0;i<dr.Length;i++)
            {
                newdt.ImportRow((DataRow)dr[i]);
            }
            return newdt;//返回的查询结果
        }

DataRow[] rows = dt.Select(conditions);
foreach(DataRow row in rows)
{
newdt.Rows.Add(row.ItemArray);
}

return newdt;