Transpose a List using c#

Question

Below is the class

public class ErrorDTO
{
    public string Type { get; set; }
    public string Data { get; set; }
}

I have list of ErrorDTO i.e List<ErrorDTO> Below is the data

Type1  x
Type1  y
Type2  z
Type3  p
Type2  q

and so on....

I want to show this in a DataGrid of xaml in the following way

Type1  Type2  Type3
 x       z       p
 y       q

How can I do this? I tried converting the list to a datatable but no luck. invalidDataList is the List<ErrorDTO>

var r = invalidDataList
            .Distinct()
            .GroupBy(x => x.Type)
            .Select(y => new { Type = y.Key, Data = y });

DataTable table = new DataTable();
foreach (var item in r)
{
    table.Columns.Add(item.Type, typeof(string));
    foreach (var i in item.Data)
    {
        DataRow dr = table.NewRow();
        dr[item.Type] = i.Data;
        table.Rows.Add(dr);
    }
}
dataGrid.ItemsSource = table.AsEnumerable();

[stack question about pivoting with linq](http://stackoverflow.com/questions/18238046/trying-to-pivot-data-using-linq) try googling around for pivot with LINQ — RadioSpace, Sep 02 '14 at 23:02
This might help to you http://stackoverflow.com/questions/320089/how-do-i-bind-a-wpf-datagrid-to-a-variable-number-of-columns — Seminda, Sep 03 '14 at 04:41

Alberto Solano · Answer 1 · 2014-09-12T13:02:25.843

The following code should help you:

lst.GroupBy (l => l.Type).Select (l => new {
       Type1 = l.Where (x => x.Type == "Type1").Select (x => x.Data),
       Type2 = l.Where (x => x.Type == "Type2").Select (x => x.Data),
       Type3 = l.Where (x => x.Type == "Type3").Select (x => x.Data)
    });

For a pivot table, in case you don't know how many types are inside the list, I think the simplest solution is to check first the distinct types we have within the lst list:

var types = lst.Select (l => l.Type).Distinct().OrderBy (l => l).ToList();

and then use a DataTable:

var dt = new DataTable("Test");
DataRow workRow;

for (int i = 0; i < types.Count; i++)
{
    dt.Columns.Add(types[i]);
    var dataToInsert = lst.Where (l => l.Type == types[i]).Select (l => l.Data).ToList();
    foreach (var element in dataToInsert)
    {
        workRow = dt.NewRow();
        workRow[types[i]] = element;
        dt.Rows.Add(workRow);
    }
}

What if I do not know how many types are there, i.e. Type1,2,3,4..etc and also what are they — user3787610, Sep 11 '14 at 18:41

score 1 · Accepted Answer · answered Sep 12 '14 at 13:46

ok, how about,

var typed = invalidDataList
                 .GroupBy(d => d.Type)
                 .Select(g => new
                     {
                         Type = g.Key,
                         Data = g.Select(d => d.Data).ToList()
                     })
                 .ToList();

var table =  new DataTable();
foreach(var type In typed)
{
    table.Columns.Add(type.Type);
}

var maxCount = typed.Max(t => t.Data.Count);
for(var i = 0; i < maxCount; i++)
{
    var row = table.NewRow();
    foreach(var type in typed)
    {
        if (type.Data.Count > i)
        {
            row[type.Type] = type.Data[i]
        }
    }

    table.Rows.Add(row);
}

Transpose a List using c#

2 Answers2