How do I list distinct values as columns?

Question

I received a request to export data from my asp.net mvc project using linq to an excel spreadsheet. Usually this is an easy task, however, in this scenario the person requesting the data would like the export from example or list A to look like example B

Example A (current export)

Id | CustomerNum | CustomerName | FruitName | Charge
____________________________________________________
1  | 1026        | Bob          | Banana    | 3.00 
2  | 1032        | Jill         | Apple     | 2.00
3  | 1026        | Bob          | Apple     | 3.00
4  | 1144        | Marvin       | Banana    | 1.00
5  | 1753        | Sam          | Pear      | 4.00
6  | 1026        | Bob          | Banana    | 3.00

Example B (requested export format)

Id | CustomerNum | CustomerName | Banana | Apple | Pear 
_________________________________________________________
1  | 1026        | Bob          | 6.00   | 3.00  |
2  | 1032        | Jill         | 0      | 2.00  |
3  | 1144        | Marvin       | 1.00   | 0     |
5  | 1753        | Sam          | 0      | 0     | 4.00

I have never seen where distinct row values were used as columns. How should I go about this?

It depends on what structure your data is in. A list of objects? A DataTable? A matrix of bytes? ;) — Heretic Monkey, Aug 02 '19 at 21:41
My data will be cast to a list something like `var result = (from f in db.Fruit where f.yada == 'yadayada').ToList();` — Skullomania, Aug 02 '19 at 21:44
@Filburt I will check out the possible duplicate. If I am able to resolve it using the information in the resolution, I will mark duplicate. — Skullomania, Aug 02 '19 at 21:56

score 2 · Accepted Answer · answered Aug 03 '19 at 00:22

Create a 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("Id", typeof(int));
            dt.Columns.Add("CustomerNum", typeof(int));
            dt.Columns.Add("CustomerName", typeof(string));
            dt.Columns.Add("FruitName", typeof(string));
            dt.Columns.Add("Charge", typeof(decimal));

            dt.Rows.Add(new object[] {1,1026, "Bob", "Banana", 3.00}); 
            dt.Rows.Add(new object[] {2,1032, "Jill", "Apple", 2.00}); 
            dt.Rows.Add(new object[] {3,1026, "Bob", "Apple", 3.00}); 
            dt.Rows.Add(new object[] {4,1144, "Marvin", "Banana", 1.00}); 
            dt.Rows.Add(new object[] {5,1753, "Sam", "Pear", 4.00}); 
            dt.Rows.Add(new object[] {6,1026, "Bob", "Banana", 3.00});

            string[] fruits = dt.AsEnumerable().Select(x => x.Field<string>("FruitName")).Distinct().OrderBy(x => x).ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("CustomerNum", typeof(int));
            pivot.Columns.Add("CustomerName", typeof(string));

            foreach (string fruit in fruits)
            {
                pivot.Columns.Add(fruit, typeof(decimal));
            }

            var groups = dt.AsEnumerable().GroupBy(x => x.Field<int>("CustomerNum"));

            foreach (var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();

                newRow["CustomerNum"] =  group.Key;
                newRow["CustomerName"] = group.First().Field<string>("CustomerName");

                foreach (DataRow row in group)
                {
                    string fruitName = row.Field<string>("FruitName");
                    decimal oldvalue = (newRow[fruitName] == DBNull.Value) ? 0 : (decimal)newRow[fruitName];
                    newRow[fruitName] = oldvalue + row.Field<decimal>("Charge");
                }
            }


        }
    }
}

How do I list distinct values as columns?

1 Answers1