.net - How to Convert Row to Column in Linq and SQL -

i have table called languagemaster having below record

language keyname keyvalue english    city    aa swedish    city    aaswedish german     city    aager chines     city    aachines french     city    aafr spanish    city    aaspanish 

how convert languagemaster table below table

keyname  english swedish   german  chines   french  spanish             city      aa     aaswedish aager   aachines aafr    aaspanish 

please let me know how write query in both sol , linq.

update: created following generic method can build pivot table collection

public static datatable topivottable<t, tcolumn, trow, tdata>(     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; } 

usage:

var table = languagemaster.topivottable(                 item => item.language,                 item => item.keyname,                 items => items.any() ? items.first().keyvalue : null); 

it has 3 parameters:

• column property selector selects columns (i.e. in column headers) in case distinct values of languages, else, date.
• row property selector - value appear in row headers, each row related to. keep in mind - expression, not simple delegate. need set column name of first column.
• data selector - method run on grouped data each cell. i.e. in case select keyvalue property of first item in group. items count items => items.count() or else.

result:

---

original answer:

this query return pivot data. each item in query have name (i.e. "city" in example) , list of values - 1 value each pivot column (i.e. each language have value containing language name column , value)

var languages = languagemaster.select(x => x.language).distinct(); var query = r in languagemaster             group r r.keyname namegroup             select new {                 name = namegroup.key,                 values = lang in languages                          join ng in namegroup                                on lang equals ng.language languagegroup                          select new {                              column = lang,                              value = languagegroup.any() ?                                       languagegroup.first().keyvalue : null                          }             }; 

how build data table query

datatable table = new datatable(); table.columns.add("keyname");  // first column foreach (var language in languages)     table.columns.add(language); // columns each language  foreach (var key in query) {     var row = table.newrow();     var items = key.values.select(v => v.value).tolist(); // data columns     items.insert(0, key.name); // data first column     row.itemarray = items.toarray();     table.rows.add(row); }