datagrid行转列
数据库中查询后的结果为：
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

------解决方案--------------------
单纯用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;
}
}
}
------解决方案--------------------
和楼上zzfloat(方舟) 兄的方法差不多，
//先将Col1,Col2取出
DataTable rs1=GetTable( "Select col1,col2 From TabName Group By Col1,Col2 ");
//将Col3取出
DataTable rs2=GetTable( "Select Col3 From TabName ");
//取出所有数据
DataTable rs3=GetTable( "Select distinct col1,col2,Col3,Col4 From TabName ");
//新表
DataTable dtnew = new DataTable() ;
dtnew.Columns.Add( "col1 " ) ;
dtnew.Columns.Add( "col2 " ) ;
//将地址显示成列
for( int i = 0 ;i <rs2.Rows.Count;i++ )
dtnew.Columns.Add( rs2.Rows[i][0].ToString() ) ;
//绑定业务
DataRow dr ;
for( int i=0;i <rs1.Rows.Count;i++)
{
dr = dtnew.NewRow() ;
dr[ "col1 "] = rs1.Rows[i][ "col1 "].ToString() ;
dr[ "col2 "] = rs1.Rows[i][ "col2 "].ToString() ;
dtnew.Rows.Add(dr) ;
}

//将金额进行绑定
DataRow[] _Dr ;
for( int i=0;i <dtnew.Rows.Count;i++)
{
for( int j=2;j <dtnew.Columns.Count;j++)
{
_Dr = rs3.Select( " col1= ' "+dtnew.Rows[i][ "col1 "].ToString()+ " ' and col2= ' "+dtnew.Rows[i][ "col2 "].ToString()+ " ' and col3= ' "+dtnew.Columns[j]+ " ' ") ;
if( _Dr.Length> 0 )
{
dtnew.Rows[i][j] = _Dr[0][ "col4 "].ToString() ;
}
}
}
dgProv.DataSource = dtnew ;
dgProv.DataBind() ;