Resolved Transpose List Items - Rows to Columns

[raajeshnrh]

Team, I'm facing a typical issue in data transformation in C#, I'm having the below list

+---------+---------------+----------+-----------+-----------+
| Country | BillingMethod | InvCount | CustCount | InvAmount |
+---------+---------------+----------+-----------+-----------+
| Germany | Paper           |         4 |          5 |      1234 |
| Germany | eInvoice       |       52 |        34 |      4567 |
| Greece    | Paper           |         8 |        75 |     78955 |
| Denmark | Paper          |        32 |        15 |      7890 |
| Norway  | eInvoice       |         4 |         52 |     65898 |
+---------+---------------+----------+-----------+-----------+

My expected result output should be as below that I want to combine the similar Country Like Germany (2nd row) to 1st row as

+---------+---------+----------+-------------+--------------+-------------+----------------+-------------+--------+
| Country | BMPaper |  BMInv   | InvCntPaper | CustCntPaper | InvAmtPaper | InvCnteInvoice | CustCnteInv | InvAmt |
+---------+---------+----------+-------------+--------------+-------------+----------------+-------------+--------+
| Germany  | Paper   | eInvoice |                 4 |            5 |               1234 |                     52 |                  34 |            4567 |
| Greece     | Paper   | Null     |                    8 |           75 |            78955 |                       0 |                    0 |                 0 |
| Denmark  | Paper   | Null     |                 32 |           15 |              7890 |                        0 |                    0 |                0 |
| Norway    | Null      | eInvoice |                0 |            0 |                     0 |                        4 |                  52 |        65898 |
+---------+---------+----------+-------------+--------------+-------------+----------------+-------------+--------+

in my application Stored Procedure may return more than 1 countries at a time like Denmary would come 2 times, India would come 2 times

I want to group the countries and make them in a single row itself.

Your help would be appreciated.

Thanks

 

[Skydiver]

Hard coded makes things easy:

using System;
using System.Collections.Generic;
using System.Linq;

var invoices = new List<Invoice>
{
    new Invoice("Germany",  BillingMethod.Paper,        InvoiceCount: 4,    CustomerCount: 5,   1234),
    new Invoice("Germany",  BillingMethod.Electronic,   InvoiceCount: 52,   CustomerCount: 34,  4567),
    new Invoice("Greece",   BillingMethod.Paper,        InvoiceCount: 8,    CustomerCount: 75,  78955),
    new Invoice("Denmark",  BillingMethod.Paper,        InvoiceCount: 32,   CustomerCount: 15,  7890),
    new Invoice("Norway",   BillingMethod.Electronic,   InvoiceCount: 4,    CustomerCount: 52,  65898),
};

Console.WriteLine("Invoices:");
foreach (var invoice in invoices)
    Console.WriteLine(invoice);

var flattened = invoices.GroupBy(invoice => invoice.Country)
                        .Select(group => new CountryInvoice(group));

Console.WriteLine("Flattened Invoices:");
foreach (var invoice in flattened)
    Console.WriteLine(invoice);


enum BillingMethod { None, Paper, Electronic }

record Invoice(string Country,
               BillingMethod BillingMethod,
               int InvoiceCount,
               int CustomerCount,
               decimal Amount);

record CountryInvoice(string Country,
                      int PaperInvoiceCount,
                      int PaperCustomerCount,
                      decimal PaperAmount,
                      int ElectronicInvoiceCount,
                      int ElectronicCustomerCount,
                      decimal ElectronicAmount)
{
    public CountryInvoice(IGrouping<string, Invoice> group)
        : this(group.Key, 0, 0, 0, 0, 0, 0)
    {
        foreach(var invoice in group)
        {
            switch(invoice.BillingMethod)
            {
                case BillingMethod.Paper:
                    PaperInvoiceCount = invoice.InvoiceCount;
                    PaperCustomerCount = invoice.CustomerCount;
                    PaperAmount = invoice.Amount;
                    break;

                case BillingMethod.Electronic:
                    ElectronicInvoiceCount = invoice.InvoiceCount;
                    ElectronicCustomerCount = invoice.CustomerCount;
                    ElectronicAmount = invoice.Amount;
                    break;
            }
        }
    }
}

Output:

Invoices:
Invoice { Country = Germany, BillingMethod = Paper, InvoiceCount = 4, CustomerCount = 5, Amount = 1234 }
Invoice { Country = Germany, BillingMethod = Electronic, InvoiceCount = 52, CustomerCount = 34, Amount = 4567 }
Invoice { Country = Greece, BillingMethod = Paper, InvoiceCount = 8, CustomerCount = 75, Amount = 78955 }
Invoice { Country = Denmark, BillingMethod = Paper, InvoiceCount = 32, CustomerCount = 15, Amount = 7890 }
Invoice { Country = Norway, BillingMethod = Electronic, InvoiceCount = 4, CustomerCount = 52, Amount = 65898 }
Flattened Invoices:
CountryInvoice { Country = Germany, PaperInvoiceCount = 4, PaperCustomerCount = 5, PaperAmount = 1234, ElectronicInvoiceCount = 52, ElectronicCustomerCount = 34, ElectronicAmount = 4567 }
CountryInvoice { Country = Greece, PaperInvoiceCount = 8, PaperCustomerCount = 75, PaperAmount = 78955, ElectronicInvoiceCount = 0, ElectronicCustomerCount = 0, ElectronicAmount = 0 }
CountryInvoice { Country = Denmark, PaperInvoiceCount = 32, PaperCustomerCount = 15, PaperAmount = 7890, ElectronicInvoiceCount = 0, ElectronicCustomerCount = 0, ElectronicAmount = 0 }
CountryInvoice { Country = Norway, PaperInvoiceCount = 0, PaperCustomerCount = 0, PaperAmount = 0, ElectronicInvoiceCount = 4, ElectronicCustomerCount = 52, ElectronicAmount = 65898 }

Running demo code in this .NET Fiddle: C# Online Compiler | .NET Fiddle

 

[raajeshnrh]

Hard coded makes things easy:

using System;
using System.Collections.Generic;
using System.Linq;

var invoices = new List<Invoice>
{
    new Invoice("Germany",  BillingMethod.Paper,        InvoiceCount: 4,    CustomerCount: 5,   1234),
    new Invoice("Germany",  BillingMethod.Electronic,   InvoiceCount: 52,   CustomerCount: 34,  4567),
    new Invoice("Greece",   BillingMethod.Paper,        InvoiceCount: 8,    CustomerCount: 75,  78955),
    new Invoice("Denmark",  BillingMethod.Paper,        InvoiceCount: 32,   CustomerCount: 15,  7890),
    new Invoice("Norway",   BillingMethod.Electronic,   InvoiceCount: 4,    CustomerCount: 52,  65898),
};

Console.WriteLine("Invoices:");
foreach (var invoice in invoices)
    Console.WriteLine(invoice);

var flattened = invoices.GroupBy(invoice => invoice.Country)
                        .Select(group => new CountryInvoice(group));

Console.WriteLine("Flattened Invoices:");
foreach (var invoice in flattened)
    Console.WriteLine(invoice);


enum BillingMethod { None, Paper, Electronic }

record Invoice(string Country,
               BillingMethod BillingMethod,
               int InvoiceCount,
               int CustomerCount,
               decimal Amount);

record CountryInvoice(string Country,
                      int PaperInvoiceCount,
                      int PaperCustomerCount,
                      decimal PaperAmount,
                      int ElectronicInvoiceCount,
                      int ElectronicCustomerCount,
                      decimal ElectronicAmount)
{
    public CountryInvoice(IGrouping<string, Invoice> group)
        : this(group.Key, 0, 0, 0, 0, 0, 0)
    {
        foreach(var invoice in group)
        {
            switch(invoice.BillingMethod)
            {
                case BillingMethod.Paper:
                    PaperInvoiceCount = invoice.InvoiceCount;
                    PaperCustomerCount = invoice.CustomerCount;
                    PaperAmount = invoice.Amount;
                    break;

                case BillingMethod.Electronic:
                    ElectronicInvoiceCount = invoice.InvoiceCount;
                    ElectronicCustomerCount = invoice.CustomerCount;
                    ElectronicAmount = invoice.Amount;
                    break;
            }
        }
    }
}

Output:

Invoices:
Invoice { Country = Germany, BillingMethod = Paper, InvoiceCount = 4, CustomerCount = 5, Amount = 1234 }
Invoice { Country = Germany, BillingMethod = Electronic, InvoiceCount = 52, CustomerCount = 34, Amount = 4567 }
Invoice { Country = Greece, BillingMethod = Paper, InvoiceCount = 8, CustomerCount = 75, Amount = 78955 }
Invoice { Country = Denmark, BillingMethod = Paper, InvoiceCount = 32, CustomerCount = 15, Amount = 7890 }
Invoice { Country = Norway, BillingMethod = Electronic, InvoiceCount = 4, CustomerCount = 52, Amount = 65898 }
Flattened Invoices:
CountryInvoice { Country = Germany, PaperInvoiceCount = 4, PaperCustomerCount = 5, PaperAmount = 1234, ElectronicInvoiceCount = 52, ElectronicCustomerCount = 34, ElectronicAmount = 4567 }
CountryInvoice { Country = Greece, PaperInvoiceCount = 8, PaperCustomerCount = 75, PaperAmount = 78955, ElectronicInvoiceCount = 0, ElectronicCustomerCount = 0, ElectronicAmount = 0 }
CountryInvoice { Country = Denmark, PaperInvoiceCount = 32, PaperCustomerCount = 15, PaperAmount = 7890, ElectronicInvoiceCount = 0, ElectronicCustomerCount = 0, ElectronicAmount = 0 }
CountryInvoice { Country = Norway, PaperInvoiceCount = 0, PaperCustomerCount = 0, PaperAmount = 0, ElectronicInvoiceCount = 4, ElectronicCustomerCount = 52, ElectronicAmount = 65898 }

Running demo code in this .NET Fiddle: C# Online Compiler | .NET Fiddle

Thanks SkyDiver...you are the best...Could you teach me the C#...how can I follow you..From here I can build other things....Once again I appreciate your kind support.

 

[raajeshnrh]

Thanks SkyDiver...you are the best...Could you teach me the C#...how can I follow you..From here I can build other things....Once again I appreciate your kind support.

Once again Thanks Skydiver...I'm from India.....which coutry are you from....please share if you have facebook or Twitter...I'll follow....All the Best and your kind hearted...I asked the same question in Stackoverflow they banned my question as its not up to the mark and prohibited to ask questions in future...I really hate that Stackoverflow.