How to Convert Row to Column in Linq and SQL

Question

i have a 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 to convert Languagemaster Table to below table

keyName  English Swedish   German  Chines   French  Spanish            
City      AA     AAswedish AAger   AAchines AAfr    AAspanish

please let me know how to write Query in both SOL and LinQ.

Answer 1

UPDATE: I created following generic method which can build pivot table from any collection

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

Usage:

var table = Languagemaster.ToPivotTable(
                item => item.language,
                item => item.keyName,
                items => items.Any() ? items.First().keyValue : null);

It has three parameters:

• column property selector which selects columns (i.e. what will be in column headers) in your case it is distinct values of languages, but it could be anything else, like date.
• row property selector - is a value which will appear in row headers, this is a what each row will be related to. Keep in mind - it is expression, not simple delegate. We need it to set column name of first column.
• data selector - this is a method which will be run on grouped data for each cell. I.e. in your case we just select keyValue property of first item in group. But it could be items count items => items.Count() or anything else.

Result:

---

ORIGINAL ANSWER:

This query will return pivot for your data. Each item in query will have Name (i.e. "City" in your example) and list of values - one value for each pivot column (i.e. for each language we will have value containing language name as Column and Value)

var languages = Languagemaster.Select(x => x.language).Distinct();
var query = from r in Languagemaster
            group r by r.keyName into nameGroup
            select new {
                Name = nameGroup.Key,
                Values = from lang in languages
                         join ng in nameGroup 
                              on lang equals ng.language into languageGroup
                         select new {
                             Column = lang,
                             Value = languageGroup.Any() ? 
                                     languageGroup.First().keyValue : null
                         }
            };

How to build data table from this query

DataTable table = new DataTable();
table.Columns.Add("keyName");  // first column
foreach (var language in languages)
    table.Columns.Add(language); // columns for each language

foreach (var key in query)
{
    var row = table.NewRow();
    var items = key.Values.Select(v => v.Value).ToList(); // data for columns
    items.Insert(0, key.Name); // data for first column
    row.ItemArray = items.ToArray();
    table.Rows.Add(row);
}

Answer 2

Here is a code to group data based on multiple columns.

        testDt = GetTestDate();
        var data2 = testDt.Tables[0].AsEnumerable().Select(x => new
        {
            Family = x.Field<int>("tdFamily"),
            Class = x.Field<short>("luClass"),
            Region = x.Field<short>("luRegion"),
            Year = x.Field<int>("tdYear"),
            Population = x.Field<decimal>("tdPopulation ")
        });

        DataTable pivotDataTable = data2.ToPivotTable(
             item => item.Year,
            item => new{ item.Family, item.Class, item.Region},
            items => items.Any() ? items.Sum(x => x.Allocation) : 0
            );

         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 rowsName = ((NewExpression)rowSelector.Body).Members.Select(s => s).ToList();
        foreach (var row in rowsName)
        {
            var name = row.Name; 
            table.Columns.Add(new DataColumn(name));
        }
        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();
            string[] keyRow = row.Key.ToString().Split(',');
            int index = 0;
            foreach (var key in keyRow)
            {
                string keyValue = key.Replace("}", "").Split('=')[1].Trim();
                items.Insert(index, keyValue);
                index++;
            }
            dataRow.ItemArray = items.ToArray();
            table.Rows.Add(dataRow);
        }
        return table;
    }
}