How to Create Pivot Table using Linq or DataTableExtension method?

Help Something Else
1

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.

3

@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:
    grafik
    grafik698×476 12.8 KB

lets use following LINQ for doing the Pivot:
grafik

grafik
grafik735×220 14.7 KB 

(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
4

Thanks a lot. Works like a charm.

Could you please explain the linq Query.

5

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.

6

grafik
grafik735×220 14.7 KB

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
7

Thanks

8

Can you suggest the resource to learn LINQ?

9

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
10

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