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]

What are the rules for creating the new columns? Is there a particular naming convention? Are there any rules about the ordering of the new columns?

 

[Skydiver]

I'm facing a typical issue in data transformation

As a side question, how is this a typical issue? The typical thing in data transformations is to normalize the data to make data storage more efficient. It looks like what you are trying to do is to de-normalize the data and make data storage less efficient.

 

[raajeshnrh]

What are the rules for creating the new columns? Is there a particular naming convention? Are there any rules about the ordering of the new columns?

there is no particular naming convention, I've tried this with LINQ but not able to get the required output by merging similar countries in single line

 

[Skydiver]

I've tried this with LINQ but not able to get the required output by merging similar countries in single line

Post your code.

 

[raajeshnrh]

Post your code.

          var grouping1 = from p in myDeserializedClass
                            .Where(a => a.ReceivedInvoice != null)
                           .SelectMany(l => l.ReceivedInvoice
                           .SelectMany(t => t.Count),
                            (x, t) => new FlatList
                            {
                                Country = x.Country,
                                InvAmount = t.InvAmount,
                                InvCount = t.InvCount,
                                CustCount = t.CustCount,
                                BillingMethod = t.BillingMethod
                            })
                            select p;

 

[Skydiver]

Perhaps, I'm just going blind, but where is the grouping attempt? Where is the attempt to create the extra columns?

Basically, the real piece of information I'm trying to tease out of you is: Are there only ever 2 well know billing methods, or could there be additional billing methods like "Guido-the-Enforcer", and are these billing methods hard coded, or are they ad hoc.

 

[raajeshnrh]

Perhaps, I'm just going blind, but where is the grouping attempt? Where is the attempt to create the extra columns?

Basically, the real piece of information I'm trying to tease out of you is: Are there only ever 2 well know billing methods, or could there be additional billing methods like "Guido-the-Enforcer", and are these billing methods hard coded, or are they ad hoc.

Sorry Skydiver, I'm just beginner and learning LINQ C#. As I said there are only 2 billing methods, one is Paper and other is eInvoice

 

[Skydiver]

Anyway, you can apply the same principles is the code in CodeProject post to build your own DataTable:

C# Pivot Table

This article explains how to read a DataTable and return an inverted or pivot table depending on the column names provided.

www.codeproject.com

 

[raajeshnrh]

Sorry Skydiver, I'm just beginner and learning LINQ C#. As I said there are only 2 billing methods, one is Paper and other is eInvoice

Hi Skydiver, Is it possible to transpose list item rows to columns.

 

[Skydiver]

Sorry Skydiver, I'm just beginner and learning LINQ C#. As I said there are only 2 billing methods, one is Paper and other is eInvoice

If you only have those two, then the problem is much easier. Why do you even have to use LINQ? Admittedly, LINQ or LINQ extension methods will make some things much easier.

 

[Skydiver]

Hi Skydiver, Is it possible to transpose list item rows to columns.

Yes it is possible. Especially if it's hard coded. The question that follows is: Do you need a list output or a DataTable . If a DataTable suffices, see post #9. The simple inversion is in that post is just a transposition.

 

[raajeshnrh]

Yes it is possible. Especially if it's hard coded. The question that follows is: Do you need a list output or a DataTable . If a DataTable suffices, see post #9. The simple inversion is in that post is just a transposition.

I prefer List output only, hard code not a problem, But I need output in List format...Thanks for your continuous support Skydiver.

 

[Skydiver]

With the initial input, will there ever be more than a single entry for a given billing method for a particular country? E.g. can you have two or more Germany entries both having paper billing?

 

[raajeshnrh]

With the initial input, will there ever be more than a single entry for a given billing method for a particular country? E.g. can you have two or more Germany entries both having paper billing?

Only 2 Billing method for each Country is possible for example :
1) Germay > BillingMethod > Paper And Germay > BillingMethod > eInvoice
2) Germay > BillingMethod > Paper Or Germay > BillingMethod > eInvoice

Please let me know if you need more information. Thanks