如何转换行到列在LINQ和SQL

问题描述

我有一个称为表的 Languagemaster 具有低于记录

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

如何 Languagemaster 表格转换为下表

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

请让我知道如何在两个SOL和LINQ查询写

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

推荐答案

更新：我创建了下面这可以从任何集合构建的数据透视表泛型方法

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

用法：

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

它有三个参数：

• 的列属性选择器的它选择列（即会在列标题的）在你的情况下，它的语言不同的值，但它可能是任何东西，如日期。


• 的行属性选择器的 - 是将出现在行标题的值，这是一个什么样的每一行会涉及到。请记住 - 这是当然pression，而不是简单的委托。我们需要它来设置第一列的列名。


• 数据选择的 - 这是将针对每个小区分组数据运行的方法。即你的情况，我们只选择A组第一项的的keyValue 属性。但它可能是物品计数项目=＆GT; items.Count（）或其他任何东西。

• 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.

结果：

原来的答复：

此查询将返回支点为您的数据。在查询中的每个项目都会有名称（即城市在你的例子）和值的列表 - 每个枢轴列（也就是因为我们将有一个包含价值每种语言有一个值语言名称列和值）

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

这篇关于如何转换行到列在LINQ和SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！