Pivot -Using LiNQ

[Balachander_Pandian]

Hi Expert,
I have an excel file which contains table state and CC Invoice value . I need to pivot the state wise t sum up
Input.xlsx (11.4 KB)
the CC invoice value. How can achieve this by using LINQ. Attached input file for your reference.

Regards,
Balachander P

[mukesh.singh]

Here’s how you can achieve state-wise sum of CC Invoice values in your Excel file using LINQ, assuming you can access the data as a DataTable object:

// Assuming your data is loaded into a DataTable named 'dataTable'

var pivotedData = (from row in dataTable.AsEnumerable()
                   group row by state: row["State"] ?? string.Empty into stateGroup
                   select new
                   {
                       State = stateGroup.Key,
                       TotalCCInvoiceValue = stateGroup.Sum(row => Convert.ToDecimal(row["CC Invoice value"]))
                   }).ToList();

Explanation:

1. from row in dataTable.AsEnumerable(): This line iterates through each row in the dataTable as an anonymous object (row).
2. group row by state: row["State"] ?? string.Empty: This groups the rows by the value in the “State” column. The null-coalescing operator (??) ensures even rows with empty state values are grouped under an empty string key.
3. into stateGroup: This assigns the grouped data to the stateGroup variable.
4. select new {...}: This selects specific data from each group and creates a new anonymous object.
   • State: The group key (state name).
   • TotalCCInvoiceValue: The sum of “CC Invoice value” for all rows in the group, converted to decimal using Convert.ToDecimal().
5. .ToList(): This converts the resulting sequence of anonymous objects into a list.

Using the Pivoted Data:

Once you have the pivotedData list, you can use it for various purposes:

• Loop through the list and display the state and total invoice value.
• Write the data to a new Excel sheet using libraries like EPPlus (for creating Excel files in C#) or similar tools based on your programming environment.

Note:

• This example assumes the “CC Invoice value” column contains numeric data. You might need to adjust the conversion logic based on your actual data type.
• Remember to replace "State" with the actual column name containing state information in your Excel file.

I hope this helps! Feel free to ask if you have any further questions or need help integrating this code into your specific environment.

[Anil_G (Anil Gorthi)]

@Balachander_Pandian

Please try this in assign

dt_Table = dt_Table.AsEnumerable.GroupBy(function(x) x("State").ToString).Select(function(x) dt_Table.LoadDataRow({x.Key,x.Sum(function(y) CDBL(y("CC Invoice Value").ToString)).ToString},True)).CopyToDataTable

@mukesh.singh - A small piece of advise…either add a note that the answer is from chatgpt…or testing before pasting the answer directly…and then give the proper details

cheers

[Balachander_Pandian]

@Anil_G Facing error. Assign: Conversion from string “” to type ‘Double’ is not valid. Please advise me

[AJ_Ask]

HI @Balachander_Pandian

You can do it with using create pivot table activity as follows

It will give you expected output like this

Hope this helps

[Anil_G (Anil Gorthi)]

@Balachander_Pandian

Are some values empty?

Cheers

[Balachander_Pandian]

@Anil_G Yes, there was an empty data row found and got resolved now. Thanks for your help.