How to Data Table get to 'Pivot table' or 'Matrix Table'

Question

This is my data table, I need to its data visualize to a 'pivot table' or 'matrix table'. So, I have a special case it is: I have two columns in the table

From Table:

Date	TeamNo	Size	Qty
8/4/2023	Team 01	S	10
8/4/2023	Team 02	M	20
8/4/2023	Team 03	L	30
8/5/2023	Team 01	M	40
8/5/2023	Team 02	M	50

To Table:

TeamNo	Size	8/4/2023	8/5/2023
Team 01	M	null	40
Team 01	S	10	null
Team 02	M	20	50
Team 03	L	30	null

I don't know how to try it. But I like to run without looping.

So this? https://stackoverflow.com/questions/14817061/transpose-a-datatable — Rand Random, Aug 04 '23 at 08:15

score 0 · Accepted Answer · answered Aug 04 '23 at 09:10

Try following :

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

namespace ConsoleApplication2
{
    class Program
    { 
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Date", typeof(DateTime));
            dt.Columns.Add("TeamNo", typeof(string));
            dt.Columns.Add("Size", typeof(string));
            dt.Columns.Add("Qty", typeof(int));

            dt.Rows.Add(new object[] { DateTime.Parse("8/4/2023"), "Team 01", "S", 10 });
            dt.Rows.Add(new object[] { DateTime.Parse("8/4/2023"), "Team 02", "M", 20 });
            dt.Rows.Add(new object[] { DateTime.Parse("8/4/2023"), "Team 03", "L", 30 });
            dt.Rows.Add(new object[] { DateTime.Parse("8/5/2023"), "Team 01", "M", 40 });
            dt.Rows.Add(new object[] { DateTime.Parse("8/5/2023"), "Team 02", "M", 50 });

            DateTime[] dates = dt.AsEnumerable().Select(x => x.Field<DateTime>("Date")).Distinct().OrderBy(x => x).ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("TeamNo", typeof(string));
            pivot.Columns.Add("Size", typeof(string));
            foreach(DateTime date in dates)
            {
                pivot.Columns.Add(date.ToString("M/d/yyyy"));
            }

            var groups = dt.AsEnumerable().GroupBy(x => new { team = x.Field<string>("TeamNo"), size = x.Field<string>("Size")});

            foreach(var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["TeamNo"] = group.Key.team;
                newRow["Size"] = group.Key.size;
                var rowDate = group.GroupBy(x => x.Field<DateTime>("Date"));
                foreach(var date in rowDate)
                {
                    newRow[date.Key.ToString("M/d/yyyy")] = date.Sum(x => x.Field<int>("Qty"));
                }
            }
        }

    }

 
}

Yep... It works....... thanks you very much – Danushka Fernando Aug 10 '23 at 13:45 — Danushka Fernando, Aug 10 '23 at 13:45

How to Data Table get to 'Pivot table' or 'Matrix Table'

1 Answers1