DataTable 实现表结构转换

表A:
－－－－－－－－－－－－－－－－－－
  InfoID FName FContext
  1 name1 c1
  1 name2 c2
  1 name3 c3
  2 name1 c4
  3 name4 c5
－－－－－－－－－－－－－－－－－－
转换成
表B：
－－－－－－－－－－－－－－－－－－－－－
  InfoID name1 name2 name3 name4
  1 c1 c2 c3 null
  2 c4 null null null
  3 null null null c5

－－－－－－－－－－－－－－－－－－－－－

先在数据库中创建表A

通过asp.net来访问数据库

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace JavaScript
{
    public partial class DD : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //获取FName集合，去掉重复
            DataTable dtFName = GetData("select DISTINCT FName from A", "FName");

            DataTable dtTable = new DataTable();
            //dtTable 添加列名
            dtTable.Columns.Add("InfoID ",typeof(int));
            for (int i = 0; i < dtFName.Rows.Count; i++)
            {
                dtTable.Columns.Add(dtFName.Rows[i][0].ToString(), typeof(string));
            }

            //获取表A的数据
            DataTable dtA=GetData("select * from A","A");

            //获取InfoID集合
            DataTable dtInfoID = GetData("select DISTINCT InfoID from A", "InfoID");

            int count=dtInfoID.Rows.Count;

            //创建表结构
            for (int i = 0; i < count; i++)
            {
                DataRow row = dtTable.NewRow();
                object[] obj = new object[dtInfoID.Rows.Count];
                obj[0] = dtInfoID.Rows[i][0];
                row.ItemArray = obj;
                dtTable.Rows.Add(row);
            }

            //循环给表填充值
            for (int i = 0; i < dtA.Rows.Count; i++)
            {
                DataRow drA = dtA.Rows[i];
                
                for (int j = 0; j < dtTable.Rows.Count; j++)
                {
                    DataRow row=dtTable.Rows[j];

                    //对应列名填充
                    if (Convert.ToInt32(row[0]) == Convert.ToInt32(drA[1]))
                    {
                        row[drA[2].ToString()] = drA[3];
                    }
                    else
                    {
                        row[drA[2].ToString()] = "null";
                    }
                    
                } 
            }

            GridView1.DataSource = dtTable;
            GridView1.DataBind();
                
        }

        //数据库访问，获取数据
        protected DataTable GetData(string sql,string tbName)
        {
            string connStr = ConfigurationManager.ConnectionStrings["FileManageConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds, tbName);
            DataTable dt = ds.Tables[tbName];
            return dt;
        }
    }
}