将DataTable行列转换通用方法

悟静 2011-12-29

展开全文

通过sql查询出来的数据，有时候列很少，行比较多，如统计每个月论坛发贴量，那么就有两列“月份”和“数量”，然后就是12行数据。这样看起来不好看，通过sql把查询结果行列转换也行，不过比较麻烦，所以可以

方法：传入一个DataTable，并设置要哪一列作为新的Colum，输出一个行列转换的DataTable。

标签： DataTable

代码片段(1)

[代码] [C#]代码

01
using System; 

02
using System.Collections.Generic; 

03
using System.Linq; 

04
using System.Text; 

05
using System.Data.SqlClient; 

06
using System.Data; 

07
namespace columnChange 

08
{ 

09
    class Program 

10
    { 

11
        static void Main(string[] args) 

12
        { 

13
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Test;User ID=××××;Password=××××"); 

14
            using (conn) 

15
            { 

16
                conn.Open(); 

17
                SqlDataAdapter apt = new SqlDataAdapter("select top 10 * from news ", conn); 

18
                DataTable tb = new DataTable(); 

19
                apt.Fill(tb); 

20
                DataTable tt = Col2Row(tb, "articleId"); 

21
  
22
            } 

23
        } 

24
        /// <summary> 

25
        /// 将dt行列转换成result 

26
        /// </summary> 

27
        /// <param name="src">原表dt</param> 

28
        /// <param name="columnHead">要作为Column的哪列</param> 

29
        /// <returns>新表result</returns> 

30
        public static DataTable ColToRow(DataTable dt, int columnHead) 

31
        { 

32
            DataTable result = new DataTable(); 

33
            DataColumn myHead = dt.Columns[columnHead]; 

34
            result.Columns.Add(myHead.ColumnName); 

35
            for (int i = 0; i < src.Rows.Count; i++) 

36
            { 

37
                result.Columns.Add(src.Rows[i][myHead].ToString()); 

38
            } 

39
            // 

40
            foreach (DataColumn col in dt.Columns) 

41
            { 

42
                if (col == myHead) 

43
                    continue; 

44
                object[] newRow = new object[dt.Rows.Count + 1]; 

45
                newRow[0] = col.ColumnName; 

46
                for (int i = 0; i < dt.Rows.Count; i++) 

47
                { 

48
                    newRow[i + 1] = dt.Rows[i][col]; 

49
                } 

50
                result.Rows.Add(newRow); 

51
            } 

52
            return result; 

53
        } 

54
  
55
        public static DataTable ColToRow(DataTable dt, string columnHead) 

56
        { 

57
            for (int i = 0; i < dt.Columns.Count; i++) 

58
            { 

59
                if (dt.Columns[i].ColumnName.ToUpper () == columnHead.ToUpper()) 

60
                    return Col2Row(dt, i); 

61
            } 

62
            return new DataTable(); 

63
        } 

64
    } 

65
}