Как преобразовать строку в столбец в Linq и SQL
У меня есть таблица под названием Languagemaster с записью ниже
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
пожалуйста, дайте мне знать, как написать Query в SOL и LinQ.
1 ответ
ОБНОВЛЕНИЕ: я создал следующий универсальный метод, который может построить сводную таблицу из любой коллекции
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);
У него есть три параметра:
- Селектор свойства столбца, который выбирает столбцы (то есть, что будет в заголовках столбцов), в вашем случае это разные значения языков, но это может быть что-то еще, например, дата.
- Селектор свойства строки - это значение, которое будет отображаться в заголовках строк, это то, к чему будет относиться каждая строка. Имейте в виду - это выражение, а не простой делегат. Нам нужно установить имя столбца первого столбца.
- селектор данных - это метод, который будет запускаться на сгруппированных данных для каждой ячейки. Т.е. в вашем случае мы просто выбираем
keyValueсвойство первого элемента в группе. Но это может быть количество предметовitems => items.Count()или что-нибудь еще.
Результат:
ОРИГИНАЛЬНЫЙ ОТВЕТ:
Этот запрос вернет сводку для ваших данных. Каждый элемент в запросе будет иметь Name (например, "Город" в вашем примере) и список значений - по одному значению для каждого столбца сводки (т.е. для каждого языка у нас будет значение, содержащее название языка как Column а также 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
}
};
Как построить таблицу данных из этого запроса
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);
}
Вот код для группировки данных на основе нескольких столбцов.
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;
}
}
public static DataTable XToPivotTable<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();
if (rowSelector.Body is NewExpression) {
var rowNames = ((NewExpression)rowSelector.Body).Members.ToList();
rowNames.ForEach(s => table.Columns.Add(new DataColumn(s.Name, s.DeclaringType.GetProperty(s.Name).PropertyType)));
} else {
var rowName = ((MemberExpression)rowSelector.Body).Member;
table.Columns.Add(new DataColumn(rowName.Name, rowName.DeclaringType.GetProperty(rowName.Name).PropertyType));
}
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(rg => new {
rg.Key,
Values = columns.GroupJoin(rg, c => c, r => columnSelector(r), (c, cg) => dataSelector(cg))
});
foreach (var row in rows) {
var dataRow = table.NewRow();
var items = TypeDescriptor.GetProperties(typeof(TRow)).Cast<PropertyDescriptor>().Select(s => s.GetValue(row.Key)).ToList();
items.AddRange(row.Values.Cast<dynamic>());
dataRow.ItemArray = items.ToArray();
table.Rows.Add(dataRow);
}
return table;
}