如何使用LINQ将行转换为列 [英] How do I convert rows into columns using LINQ

问题描述

您如何将所有行转换为列，

考虑第一列将使用LINQ生成列名称。如果您需要更多澄清，请告诉我。如果产品是重复的，也应该是总和。



i有一个包含以下数据的列表

Hi how do i translate all rows into columns ,
considering first column will generate as columns name using LINQ. let me if you need more clarification. also should be sum if product is duplicate.

i have a list contains below data

Product     Y1   Y2  Y3  Y4  Y5  Y6  Y7  Y8  Y9  Y10
Product1    1    2   3   4   5   6   7   8   9   10
Product2    2    3   4   5   6   7   8   9   10  11
Product3    3    4   5   6   7   8   9   10  11  12
Product4    4    5   6   7   8   9   10  11  12  13

i需要低于产量作为清单

i need to get below output as a list

YKey Year  Product1 Product2    Product3
Y1  2016    1   2   3   4
Y2  2017    2   3   4   5
Y3  2018    3   4   5   6
Y4  2019    4   5   6   7
Y5  2020    5   6   7   8
Y6  2021    6   7   8   9
Y7  2022    7   8   9   10
Y8  2023    8   9   10  11
Y9  2024    9   10  11  12
Y10 2025    10  11  12  13

我尝试过：

What I have tried:

private List<Data> CreateColumnData()
    {
        var list = new List<Data>();

        list.Add(new Data() { ProductName = "Product1", Year1 = 1, Year2  = 2, Year3 = 3, Year4 = 4, Year5 = 5, Year6 = 6, Year7 = 7,Year8 = 8,Year9=9,Year10=10});
        list.Add(new Data() { ProductName = "Product2", Year1 = 2, Year2 = 3, Year3 = 4, Year4 = 5, Year5 = 6, Year6 = 7, Year7 = 8, Year8 = 9, Year9 = 10, Year10 = 11 });
        list.Add(new Data() { ProductName = "Product3", Year1 = 3, Year2 = 4, Year3 = 5, Year4 = 6, Year5 = 7, Year6 = 8, Year7 = 9, Year8 = 10, Year9 = 11, Year10 = 12 });
        list.Add(new Data() { ProductName = "Product4", Year1 = 4, Year2 = 5, Year3 = 6, Year4 = 7, Year5 = 8, Year6 = 9, Year7 = 10, Year8 = 11, Year9 = 12, Year10 = 13});

        return list;
    }

 public class Data
    {
        public string ProductName { get; set; }
        public int Year1 { get; set; }
        public int Year2 { get; set; }
        public int Year3 { get; set; }
        public int Year4 { get; set; }
        public int Year5 { get; set; }
        public int Year6 { get; set; }
        public int Year7 { get; set; }
        public int Year8 { get; set; }
        public int Year9 { get; set; }
        public int Year10 { get; set; }
        public int Year11 { get; set; }
        public int Year12 { get; set; }
        public int Year13 { get; set; }
        public int Year14 { get; set; }
        public int Year15 { get; set; }
    }

推荐答案

试试这个：

Try this:

var result = list.Select(x => new
                    {
                    //convert cols to list of rows
                    RowData = new List<Tuple<string, string, int>>()
                        {
                        Tuple.Create(x.ProductName, "Y1", x.Year1),
                        Tuple.Create(x.ProductName, "Y2", x.Year2),
                        Tuple.Create(x.ProductName, "Y3", x.Year3),
                        Tuple.Create(x.ProductName, "Y4", x.Year4),
                        Tuple.Create(x.ProductName, "Y5", x.Year5),
                        Tuple.Create(x.ProductName, "Y6", x.Year6),
                        Tuple.Create(x.ProductName, "Y7", x.Year7),
                        Tuple.Create(x.ProductName, "Y8", x.Year8),
                        Tuple.Create(x.ProductName, "Y9", x.Year9),
                        Tuple.Create(x.ProductName, "Y10", x.Year10),
                        Tuple.Create(x.ProductName, "Y11", x.Year11),
                        Tuple.Create(x.ProductName, "Y12", x.Year12),
                        Tuple.Create(x.ProductName, "Y13", x.Year13),
                        Tuple.Create(x.ProductName, "Y14", x.Year14),
                        Tuple.Create(x.ProductName, "Y15", x.Year15)
                        }
                    })
                    //get one result list
                    .SelectMany(x=>x.RowData)
                    //group data by year
                    .GroupBy(x=>x.Item2)
                    //finally get pivoted data
                    .Select((grp, counter)=>new
                        {
                        YKey = grp.Key,
                        Year = 2016+counter,
                        P1 = grp.Where(y=>y.Item1=="Product1").Select(y=>y.Item3).SingleOrDefault(),
                        P2 = grp.Where(y=>y.Item1=="Product2").Select(y=>y.Item3).SingleOrDefault(),
                        P3 = grp.Where(y=>y.Item1=="Product3").Select(y=>y.Item3).SingleOrDefault(),
                        P4 = grp.Where(y=>y.Item1=="Product4").Select(y=>y.Item3).SingleOrDefault(),
                        });

结果：

Result:

YKey Year P1  P2  P3  P4
Y1   2016  1  2  3  4 
Y2   2017  2  3  4  5 
Y3   2018  3  4  5  6 
Y4   2019  4  5  6  7 
Y5   2020  5  6  7  8 
Y6   2021  6  7  8  9 
Y7   2022  7  8  9  10 
Y8   2023  8  9  10 11 
Y9   2024  9  10 11 12 
Y10  2025  10 11 12 13 
Y11  2026  0  0  0  0 
Y12  2027  0  0  0  0 
Y13  2028  0  0  0  0 
Y14  2029  0  0  0  0 
Y15  2030  0  0  0  0

你要 transpose 你的数据。

分2步，我会 UNPIVOT 数据，然后 PIVOT 您想要的数据。

否则，一个小程序可以执行转置操作。

a 转置程序看起来像：

you want to transpose your data.
In 2 steps, I would UNPIVOT the data, then PIVOT the data the way you want.
Otherwise, a little program can do the transpose operation.
a transpose program would look something like:

var trans = {};
for (col=1; col < list[0].size; col++) {
    tmp= {};
    tmp.Add(["Year", list[0][col].key]);
    for (row=0; col < list.size; row++) {
        tmp.Add([list[row][0].value], list[row][col].value]);
    }
    trans.Add(tmp);
}

语言语法详细信息留给您。

Language syntax details are left to you.

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