How to Create Pivot Table using Linq or DataTableExtension method?

I have a table like this

 

DEPT CODE AMOUNT
SALES 01 1500
FINANCE 01 2000
IT 01 3000
SALES 01 4000
IT 02 1200
SALES 02 1100
FINANCE 03 1400


Need to make pivot like this

 

Row Labels FINANCE IT SALES
01 2000 3000 5500
02
1200 1100
03 1400


Suggest a way by using LINQ.

@kumar.varun2

Welcome to the forum

lets prepare the report datastructure

  • calculate the columns names by the distinct dept names
  • by adding dynamicly the columns to the report datatable structure starting with the row_label column:

lets use following LINQ for doing the Pivot:
grafik

(From d In dtData.AsEnumerable
Group d By k=d("CODE").toString Into grp=Group
Let dv = (From dp In Depts
              Let f =  grp.Where(Function (f2) f2("DEPT").toString.Equals(dp)).Sum(Function (s) CInt("0" & s("AMOUNT").toString))
			  Select t=Tuple.Create(dp, f.toString)).ToDictionary(Of String, String)(Function (t) t.Item1,Function (t) t.Item2)
Let va = Depts.Select(Function (x) If(dv.ContainsKey(x), dv(x), Nothing)).toArray
Let ra = va.Prepend(k).toArray
Select dtReport.Rows.Add(ra)).CopyToDataTable

Input / Output:
grafik

Find starter help here.
TransformData_toPivot.xaml (11.7 KB)

1 Like

Thanks a lot. Works like a charm.

Could you please explain the linq Query.

The above solution converts the integer values to string. Is there any way to preserve the format e.g., if the number is in integer format it should be integer or if it is in double it should be double but not string.

The main idea is about:

  • group the rows by the code col value
  • create a dictionary for the DEPT dstinct names for each group (key: name, value: sum)
  • construct a part rowarray for the depts (look into the dict and retrieve the sum if it is available)
  • add the first col value to the constructed part rowarray = ra
  • ra for adding the data to the report datatable
2 Likes

Thanks

Can you suggest the resource to learn LINQ?

https://linqsamples.com/

highly recommended is also the debugging course from UiPath Academy. Working with the watch / immediate panel allows very quickly to test and explore code / LINQ statements

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.