LINQ Group Rows Into Columns

Question

How can I group an up to 7 number of rows into a single row where the set columns determine the grouping?

I dont have fix rows.

I saw Stack overflow already has this question but answer is in SQL.

I need LINQ. I dont know how can I achieve this ?

Please have a look at StackOverflow link below

Table 

Id  RefId  Name   
------------------------
 1    1    Test1    
 2    1    Test2    
 3    1    Test3    
 4    2    Test4    
 5    2    Test5

So, Output would be ..

RefId  Name1  Name2  Name3  Name4 Name5 Name6 Name7  Id1 Id2 Id3 Id4  Id5  Id6  Id7
-----------------------------------------------------------------------------------
 1     Test1  Test2  Test4  null  null  null  null   1   2   3   null null null null
 2     Test4  Test5   null  null  null  null  null   4   5  null null null null null

Here, it is fixed that rows comes up to 7 .. not more than that

Sounds like you want a pivot but it's unclear whether it should be dynamic or static? Can you post sample data, desired output, and whether or not the row/column values will change — D Stanley, Jun 04 '14 at 13:09

score 2 · Answer 1 · answered Jun 04 '14 at 13:17

Something as simple as this maybe?

var persons = new []{
        new Person{Name = "John", Link = "L1"},
        new Person{Name = "John", Link = "L2"},
        new Person{Name = "John", Link = "L3"},
        new Person{Name = "Steve", Link = "L1"},
        new Person{Name = "Steve", Link = "L2"},
    };

var grouped = persons.GroupBy(p => p.Name)
                     .Select(g => new{Name = g.Key, Links = g.Select(x => x.Link)});

score 0 · Answer 2 · answered Jun 05 '14 at 06:41

Below is solution that covers your case:

DataTable dtInput = new DataTable();
dtInput.Columns.Add("Id");
dtInput.Columns.Add("RefId");
dtInput.Columns.Add("Name");

DataTable dtOutput = new DataTable();
dtOutput.Columns.Add("RefId");
Enumerable.Range(1,7).ToList()
    .ForEach( i => { dtOutput.Columns.Add("Name"+i ); dtOutput.Columns.Add("Id"+i ); }  );

dtInput.Rows.Add( new object[] { "1", "1", "Test1" } );
dtInput.Rows.Add( new object[] { "2", "1", "Test2" } );
dtInput.Rows.Add( new object[] { "3", "1", "Test3" } );
dtInput.Rows.Add( new object[] { "4", "2", "Test4" } );
dtInput.Rows.Add( new object[] { "5", "2", "Test5" } );

dtInput.AsEnumerable()
    .GroupBy(x => new { RefId = x["RefId"] })
    .Select(x => new { RefId = x.Key.RefId, 
                        Names = x.Select(x1 => x1["Name"]), 
                        Ids = x.Select(x1 => x1["Id"])})
    .ToList().ForEach( g =>
        {
            var row = dtOutput.NewRow();
            row["RefId"] = g.RefId;
            g.Names.Select( (n, idx) => new { idx = idx + 1, name = n } )
                .ToList().ForEach( n => row["Name"+n.idx] = n.name );
            g.Ids.Select( (i, idx) => new { idx = idx + 1, id = i } )
                .ToList().ForEach( i => row["Id"+i.idx] = i.id );
            dtOutput.Rows.Add( row );
        });

LINQ Group Rows Into Columns

2 Answers2