How to pivot data in LINQ without hard coding columns

Question

i have a table in sql server like this

CodeItemsRule Table 
CodeID  ItemID RuleID 
00009   D1      2   
00009   D2      2
00009   D3      1
00008   D1      3
00007   D3      1
00007   D4      1
00010   D3      2
00010   D1      1
00010   D2      1

I need to pivot this data in LINQ like this.

CodeID  D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14
00007          1  1
00009   2   2  1

given below is my code but it does not convert it into pivot format.

List<CodeItemsRule> _CodeItemRules = new CodeItemsRuleRepository.GetAll();
var _GroupResult = _CodeItemRules.GroupBy(g => g.CodeID).Select(g => new 
{
    CodeID = g.Key,
    ItemRulesList = g.ToDictionary(t => t.ItemID, t => t.RuleID)
}).ToList()

"*but this does half of the job*" What's the other half? Converting to `DataTable`? That's not LINQ task. — Ivan Stoev, May 16 '16 at 09:30
@Ivan Stoev actually my linq code does not convert data into pivot format and i need to convert it into pivot format i have modified my question. — 3355307, May 16 '16 at 09:34
Try the solution 1 provided in http://techbrij.com/pivot-c-array-datatable-convert-column-to-row-linq — Parthasarathy, May 16 '16 at 09:54

M.S. · Accepted Answer · 2016-05-16T10:01:11.600

I've simplified the solution for you by modifying the details from the link.

First of all create an extension method ToPivotTable in a static class:

public static class PivotClass
{
    public static DataTable ToPivotTable<T, TColumn, TRow, TData>(this IEnumerable<T> source,
  Func<T, TColumn> columnSelector,
  Expression<Func<T, TRow>> rowSelector,
  Func<IEnumerable<T>, TData> dataSelector)
    {
        DataTable table = new DataTable();
        var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
        table.Columns.Add(new DataColumn(rowName));
        var columns = source.Select(columnSelector).Distinct();

        foreach (var column in columns)
            table.Columns.Add(new DataColumn(column.ToString()));

        var rows = source.GroupBy(rowSelector.Compile())
                         .Select(rowGroup => new
                         {
                             Key = rowGroup.Key,
                             Values = columns.GroupJoin(
                                 rowGroup,
                                 c => c,
                                 r => columnSelector(r),
                                 (c, columnGroup) => dataSelector(columnGroup))
                         });

        foreach (var row in rows)
        {
            var dataRow = table.NewRow();
            var items = row.Values.Cast<object>().ToList();
            items.Insert(0, row.Key);
            dataRow.ItemArray = items.ToArray();
            table.Rows.Add(dataRow);
        }

        return table;
    }
}

Then, use this extension method on the list to get your pivot DataTable as mentioned below:

List<CodeItemsRule> _CodeItemRules = new List<CodeItemsRule>()
{
    new CodeItemsRule()
    {
        CodeID="00009",
        ItemID="D1",
        RuleID=2
    },new CodeItemsRule()
    {
        CodeID="00009",
        ItemID="D2",
        RuleID=2
    },new CodeItemsRule()
    {
        CodeID="00009",
        ItemID="D3",
        RuleID=1
    },new CodeItemsRule()
    {
        CodeID="00008",
        ItemID="D1",
        RuleID=3
    },new CodeItemsRule()
    {
        CodeID="00007",
        ItemID="D3",
        RuleID=1
    },new CodeItemsRule()
    {
        CodeID="00007",
        ItemID="D4",
        RuleID=1
    },new CodeItemsRule()
    {
        CodeID="00010",
        ItemID="D3",
        RuleID=2
    },new CodeItemsRule()
    {
        CodeID="00010",
        ItemID="D1",
        RuleID=1
    },new CodeItemsRule()
    {
        CodeID="00010",
        ItemID="D2",
        RuleID=1
    }

};
var pivotTable = _CodeItemRules.ToPivotTable(
    item => item.ItemID,
    item => item.CodeID,
    items => items.Any() ? items.Sum(x => x.RuleID) : 0);

Fantastic solution it worked for me. CodeID and ItemID are foreign keys as they have description in their parent table. Is it possible to display description instead of ID. in column 1 for all rows as well as description for all column headers instead of D1,D2,D3.. — 3355307, May 16 '16 at 11:36
How to customize `ToPivotTable()` function as a result it can accept multiple fields name on which grouping will be perform....any idea would be appreciated. thanks — , Sep 15 '21 at 13:54

How to pivot data in LINQ without hard coding columns

1 Answers1