将行交换为列 [英] Swapping rows as a Columns

问题描述

我有如下所示的数据表；

I have Datatable like below;

Systime       |   Name | Value
-----------------------------
 01.04.2017   Water       12
 01.04.2017   Bread       14
 01.04.2017   Chair       11
 02.04.2017   Water       25
 02.04.2017   Bread       16
 02.04.2017   Chair       21

并且我想使用linq或其他方式将行与下面的列交换；

and I want to swap rows with columns like below using linq or etc;

   systime | Water | Bread | Chair
-------------------------------
01.04.2017     12     14      11
02.04.2017     25     16      21

推荐答案

以下是构建数据透视表的方式

Here is how you build your pivot table

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

namespace ConsoleApplication1
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Systime", typeof(DateTime));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Value", typeof(int));

            dt.Rows.Add(new object[] { DateTime.Parse("01/04/2017"),"Water", 12});
            dt.Rows.Add(new object[] { DateTime.Parse("01/04/2017"),"Bread", 14});
            dt.Rows.Add(new object[] { DateTime.Parse("01/04/2017"),"Chair", 11});
            dt.Rows.Add(new object[] { DateTime.Parse("02/04/2017"),"Water", 25});
            dt.Rows.Add(new object[] { DateTime.Parse("02/04/2017"),"Bread", 16});
            dt.Rows.Add(new object[] { DateTime.Parse("02/04/2017"),"Chair", 21});

            string[] items = dt.AsEnumerable().Select(x => x.Field<string>("Name")).Distinct().ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Systime", typeof(DateTime));
            foreach(string item in items)
            {
                pivot.Columns.Add(item, typeof(int));
            }

            var groups = dt.AsEnumerable().GroupBy(x => x.Field<DateTime>("Systime")).ToList();

            int index = 0;
            foreach (var group in groups)
            {

                DataRow newRow = pivot.Rows.Add();
                newRow[0] = group.Key;
                foreach (string item in items)
                {
                    newRow[item] = group.Where(x => x.Field<string>("Name") == item).Select(x => x.Field<int>("Value")).Sum();
                }
            }

        }
    }


}

这篇关于将行交换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！