LINQ中如何实现纵表转横表?

[pipghost118]

如题
表结构
ID1 CODE AMOUNT
M1 CASH 90
M1 NETS 50

转换为
id CASH NETS
M1 90 50

[q107770540]

http://blog.csdn.net/q107770540/archive/2011/03/23/6270693.aspx

[pipghost118]

[Quote=引用 1 楼 q107770540 的回复:]

http://blog.csdn.net/q107770540/archive/2011/03/23/6270693.aspx
[/Quote]
楼上的兄弟，看了你给的文章。
有个问题，我的resault（即code）不知道有多少种，不是只有胜负两种。
所以我不太可能写死在select new中。

[threenewbee]

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("code");
            dt.Columns.Add("amount");
            dt.Rows.Add("m1", "cash", "90");
            dt.Rows.Add("m1", "nets", "50");
            Console.WriteLine("Source:");
            DisplayTable(dt);
            Console.WriteLine("Target:");
            DisplayTable(ConvertDataTable(dt));

        }

        static DataTable ConvertDataTable(DataTable source)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            var columns = from x in source.Rows.Cast<DataRow>() select new DataColumn(x[1].ToString());
            foreach (var item in columns) dt.Columns.Add(item);
            var data = from x in source.Rows.Cast<DataRow>()
                       group x by x[0] into g
                       select new { Key = g.Key.ToString(), Items = g };
            data.ToList().ForEach(x => 
                {
                    string[] array = new string[dt.Columns.Count];
                    array[0] = x.Key;
                    for (int i = 1; i < dt.Columns.Count; i++)
                        array[i] = (from y in x.Items 
                                    where y[1].ToString() == dt.Columns[i].ToString() select y[2].ToString())
                                   .SingleOrDefault();
                    dt.Rows.Add(array);
                });
            return dt;
        }

        static void DisplayTable(DataTable dt)
        { 
            dt.Columns.Cast<DataColumn>().ToList().ForEach(x => Console.Write(x + "\t"));
            Console.WriteLine();
            dt.Rows.Cast<DataRow>().ToList().ForEach(x =>
                {
                    x.ItemArray.ToList().ForEach(y => Console.Write(y.ToString() + "\t"));
                    Console.WriteLine();
                });
        }
    }
}

Source:
id code amount
m1 cash 90
m1 nets 50
Target:
id cash nets
m1 90 50
Press any key to continue . . .

[threenewbee]

修正下程序

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("code");
            dt.Columns.Add("amount");
            dt.Rows.Add("m1", "cash", "90");
            dt.Rows.Add("m1", "nets", "50");
            dt.Rows.Add("m2", "cash", "190");
            dt.Rows.Add("m2", "nets", "150");
            dt.Rows.Add("m2", "another", "222");
            dt.Rows.Add("m3", "cash", "150");
            dt.Rows.Add("m3", "another", "100");
            Console.WriteLine("Source:");
            DisplayTable(dt);
            Console.WriteLine("Target:");
            DisplayTable(ConvertDataTable(dt));

        }

        static DataTable ConvertDataTable(DataTable source)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            var columns = (from x in source.Rows.Cast<DataRow>() select x[1].ToString()).Distinct();
            foreach (var item in columns) dt.Columns.Add(item);
            var data = from x in source.Rows.Cast<DataRow>()
                       group x by x[0] into g
                       select new { Key = g.Key.ToString(), Items = g };
            data.ToList().ForEach(x => 
                {
                    string[] array = new string[dt.Columns.Count];
                    array[0] = x.Key;
                    for (int i = 1; i < dt.Columns.Count; i++)
                        array[i] = (from y in x.Items
                                    where y[1].ToString() == dt.Columns[i].ToString()
                                    select y[2].ToString())
                                   .SingleOrDefault();
                    dt.Rows.Add(array);
                });
            return dt;
        }

        static void DisplayTable(DataTable dt)
        { 
            dt.Columns.Cast<DataColumn>().ToList().ForEach(x => Console.Write(x + "\t"));
            Console.WriteLine();
            dt.Rows.Cast<DataRow>().ToList().ForEach(x =>
                {
                    x.ItemArray.ToList().ForEach(y => Console.Write(y.ToString() + "\t"));
                    Console.WriteLine();
                });
        }
    }
}

Source:
id      code    amount
m1      cash    90
m1      nets    50
m2      cash    190
m2      nets    150
m2      another 222
m3      cash    150
m3      another 100
Target:
id      cash    nets    another
m1      90      50
m2      190     150     222
m3      150             100
Press any key to continue . . .

[pipghost118]

[Quote=引用 4 楼 caozhy 的回复:]

修正下程序
C# code
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
class Program
{
static void M……
[/Quote]
功能已经自己实现，通过数组，循环的方式搞定的，做法和caozhy的差不多，
非常感谢caozhy的答复，对我非常有帮助。
从你给的code可以看出linq在实现该需求的时候并不能使得代码很简洁，同样需要配合循环等动作来实现。

[threenewbee]

[Quote=引用 5 楼 pipghost118 的回复:]
引用 4 楼 caozhy 的回复:

修正下程序
C# code
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
class Program
{……
[/Quote]
呵呵，可能我比较菜，你没有把DisplayTable()和Main()的代码也计算在内吧，期待你把你简洁的方法贴出来，让我也学习下。

[pipghost118]

呵呵 caozhy 的code已经很简洁了，我开始只是以为linq能做到更简洁，毕竟不太熟linq。