.net行转列实现(动态列)

 protected void SubtotalCalculate()
        {
            string connectionString = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();

            MySqlConnection cn = new MySqlConnection(connectionString);          
            cn.Open();

            string sqlstr = @"
                            select a.*,
                                stuff((select ',' + t.task from table2 as t where t.name = a.name for xml path('')),
                                    1,1,'') as task
                            from table1 as a

                         ";
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = sqlstr;
            MySqlDataReader reader = cmd.ExecuteReader();

            DataTable dt = ReaderToTable(reader);
            dt = DealTable(dt);
            string htmlStr = GetHtmlStrByDataTable(dt);
            //lblTable.Text = htmlStr;

        }
        protected DataTable ReaderToTable(MySqlDataReader dr)
        {
            DataTable dt = new DataTable();

            for (int i = 0; i < dr.FieldCount; i++)
            {
                dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
            }

            object[] objValues = new object[dr.FieldCount];
            while (dr.Read())
            {
                dr.GetValues(objValues);
                dt.LoadDataRow(objValues, true);
            }
            dr.Close();

            return dt;
        }
        protected DataTable DealTable(DataTable dt)
        {
            //获取task列数据，存入list
            List<string[]> list = new List<string[]>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string[] strs = dt.Rows[i]["task"].ToString().Split(',');
                list.Add(strs);
            }
            //取得strs中最长的长度
            int maxlen = 0;
            foreach (string[] strs in list)
            {
                if (strs.Length > maxlen)
                {
                    maxlen = strs.Length;
                }
            }
            //删除task列
            dt.Columns.Remove("task");
            //添加maxlen个列
            for (int i = 0; i < maxlen; i++)
            {
                dt.Columns.Add("task" + (i + 1).ToString());
            }
            //给新添加的列赋值数据
            int newColStart = dt.Columns.IndexOf(dt.Columns["name"]) + 1;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = newColStart; j < newColStart + maxlen; j++)
                {
                    int newColNo = j - newColStart;
                    if (list[i].Length > newColNo)
                    {
                        dt.Rows[i][j] = list[i][newColNo];
                    }
                }
            }

            return dt;
        }
        protected string GetHtmlStrByDataTable(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();

            //表头
            sb.Append("<tr>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sb.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
            }
            sb.Append("</tr>");

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append("<tr>");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    sb.Append("<td>" + dt.Rows[i][j].ToString() + "</td>");
                }
                sb.Append("</tr>");
            }

            return sb.ToString();
        }