how do i convert rows into columns using LINQ [Resolved]

Posted by Santosh4u under LINQ on 5/13/2016 | Points: 10 | Views : 796 | Status : [Member] | Replies : 1
Hi how do i translate all rows into columns , with sum of product if duplicate product
considering first column will generate as columns name using LINQ. let me if you need more clarification.

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
Product1 2 3 4 5 6 7 8 9 10 11
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 need to get below output as a list

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

i have this below list
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;
}





Responses

Posted by: Rajnilari2015 on: 7/4/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Santosh4u Sir, please try this

using System;
using System.Collections.Generic;

namespace ConsoleApplication3
{
class Program
{

static void Main(string[] args)
{
//unpivot records
var unpivotRecords = new
{
Product1 = Unpivot(0)
,
Product2 = Unpivot(1)
,
Product3 = Unpivot(2)
,
Product4 = Unpivot(3)
};

//pivot records
var pivotRecords = Pivot(unpivotRecords);

//display the records
pivotRecords.ForEach
(
i =>

Console.WriteLine("YKey = {0} Year = {1} Product1 = {2} Product2 = {3} Product3 = {4} Product4 = {5}",
i.YKey, i.Year, i.Product1, i.Product2, i.Product3, i.Product4)
);

Console.ReadKey();
}


#region Methods

//Source Records
private static List<InputData> GetSourceRecords()
{
List<InputData> source = new List<InputData>();
source.Add(new InputData() { ProductName = "Product1", Year1 = 1, Year2 = 2, Year3 = 3, Year4 = 4, Year5 = 5, Year6 = 6, Year7 = 7, Year8 = 8, Year9 = 9, Year10 = 10 });
source.Add(new InputData() { ProductName = "Product2", Year1 = 2, Year2 = 3, Year3 = 4, Year4 = 5, Year5 = 6, Year6 = 7, Year7 = 8, Year8 = 9, Year9 = 10, Year10 = 11 });
source.Add(new InputData() { ProductName = "Product3", Year1 = 3, Year2 = 4, Year3 = 5, Year4 = 6, Year5 = 7, Year6 = 8, Year7 = 9, Year8 = 10, Year9 = 11, Year10 = 12 });
source.Add(new InputData() { ProductName = "Product4", Year1 = 4, Year2 = 5, Year3 = 6, Year4 = 7, Year5 = 8, Year6 = 9, Year7 = 10, Year8 = 11, Year9 = 12, Year10 = 13 });
return source;
}

//Unpivot records
private static Dictionary<string, List<int>> Unpivot(int row)
{
var source = GetSourceRecords();
List<int> yearwiseProductValues = new List<int>();
var multiKeyDictionary = new Dictionary<string, List<int>>();

yearwiseProductValues.Add(source[row].Year1);
yearwiseProductValues.Add(source[row].Year2);
yearwiseProductValues.Add(source[row].Year3);
yearwiseProductValues.Add(source[row].Year4);
yearwiseProductValues.Add(source[row].Year5);
yearwiseProductValues.Add(source[row].Year6);
yearwiseProductValues.Add(source[row].Year7);
yearwiseProductValues.Add(source[row].Year8);
yearwiseProductValues.Add(source[row].Year9);
yearwiseProductValues.Add(source[row].Year10);


multiKeyDictionary.Add(source[row].ProductName, yearwiseProductValues);
return multiKeyDictionary;
}

//Pivot records
private static List<OutputData> Pivot(dynamic unpivotRecords)
{
int count = 10;
int startYear = 2016;
var pivotRecords = new List<OutputData>();

for (int i = 0; i < count; i++)
{
pivotRecords.Add(new OutputData
{
YKey = "Y" + (i + 1)
,
Year = startYear + i
,
Product1 = unpivotRecords.Product1["Product1"][i]
,
Product2 = unpivotRecords.Product2["Product2"][i]
,
Product3 = unpivotRecords.Product3["Product3"][i]
,
Product4 = unpivotRecords.Product4["Product4"][i]
});
}
return pivotRecords;
}

#endregion
}


#region Data Structures
internal class OutputData
{
public string YKey { get; internal set; }
public int Year { get; internal set; }
public int Product1 { get; internal set; }
public int Product2 { get; internal set; }
public int Product3 { get; internal set; }
public int Product4 { get; internal set; }

}

internal class InputData
{
public string ProductName { get; internal set; }
public int Year1 { get; internal set; }
public int Year2 { get; internal set; }
public int Year3 { get; internal set; }
public int Year4 { get; internal set; }
public int Year5 { get; internal set; }
public int Year6 { get; internal set; }
public int Year7 { get; internal set; }
public int Year8 { get; internal set; }
public int Year9 { get; internal set; }
public int Year10 { get; internal set; }
}

#endregion
}


Output
----------

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


hope that helps


--
Thanks & Regards,
RNA Team

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response