You must Sign In to post a response.
  • Category: LINQ

    How do i convert rows into columns using LINQ

    Hi how do i translate all rows into columns ,
    considering first column will generate as columns name using LINQ. let me if you need more clarification.

    i have a list contains below data
    Product Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10
    Product1 1 2 3 4 5 6 7 8 9 10
    Product1 2 3 4 5 6 7 8 9 10 11
    Product2 2 3 4 5 6 7 8 9 10 11
    Product3 3 4 5 6 7 8 9 10 11 12
    Product4 4 5 6 7 8 9 10 11 12 13

    i need to get below output as a list

    YKey Year Product1 Product2 Product3
    Y1 2016 3 2 3 4
    Y2 2017 5 3 4 5
    Y3 2018 7 4 5 6
    Y4 2019 9 5 6 7
    Y5 2020 11 6 7 8
    Y6 2021 13 7 8 9
    Y7 2022 15 8 9 10
    Y8 2023 17 9 10 11
    Y9 2024 19 10 11 12
    Y10 2025 21 11 12 3

    i have just given 4 products as a example but i may have many rows.
    i need it urgent
    Thanks
  • #765916
    You can use below Class to Pivot Table

    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;
    }

    you can use like ..

    var pivotTable = lstProduct_Info.ToPivotTable(
    item => item.Year,
    item => item.Product,
    items => items.Any() ? items.Sum(x => x.Sales) : 0);

  • #765934
    Hi Santosh,

    Refer below code.
    in below code,row refers to the first index and column to the inner ( second) index. e.g. values[row][column]

    public static List<List<T>> Transpose<T>(this List<List<T>> values)
    {
    if (values.Count == 0 || values[0].Count == 0)
    {
    return new List<List<T>>();
    }

    int ColumnCount = values[0].Count;

    var listByColumns = new List<List<T>>();
    foreach (int columnIndex in Enumerable.Range(0, ColumnCount))
    {
    List<T> valuesByColumn = values.Select(value => value[columnIndex]).ToList();
    listByColumns.Add(valuesByColumn);
    }
    return listByColumns;
    }
    Actually the word row and column is just our convention of thinking about the data in rows and columns , and sometimes adds more confusion than solving them.

    We are actually just swapping the inner index for the outer index. (or flipping the indexes around). So one could also just define the following extension method. . Again I borrowed from above solutions, just put it into something I find readable and fairly compact.

    Checks that the inner lists are of equal sized are required.

    public static List<List<T>> InsideOutFlip<T>(this List<List<T>> values)
    {
    if (values.Count == 0 || values[0].Count == 0)
    {
    return new List<List<T>>();
    }

    int innerCount = values[0].Count;

    var flippedList = new List<List<T>>();
    foreach (int innerIndex in Enumerable.Range(0, innerCount))
    {
    List<T> valuesByOneInner = values.Select(value => value[innerIndex]).ToList();
    flippedList.Add(valuesByOneInner);
    }
    return flippedList;
    }

  • #766185
    Hi

    You have to use pivot column.

    You can following Link
    http://stackoverflow.com/questions/17971921/how-to-convert-row-to-column-in-linq-and-sql

    hope it will help.


  • Sign In to post your comments