Unpivot multiple columns

Question

I'm a SQL developer and little exp on c# . I have unpivot result using the SQL query.

I wanted to give a try in C#

I need help to unpivot the list in the below format

 

public class TDResponse

{

public string CenterID { get; set; }

public string Center { get; set; }

public string Yr { get; set; }

public string Mth { get; set; }

public string Test_S { get; set; }

public string Test_U { get; set; }

public string Dev_S { get; set; }

public string Dev_U { get; set; }

}

private static List<TDResponse> CreateDatSample()

{

List<TDResponse> lst = new List<TDResponse>();

lst.Add(new TDResponse { CenterID = "491", Center = "Enterprise", Yr = "2020", Mth = "APRIL", Test_S = "0", Test_U = "3", Dev_S = "45", Dev_U = "0" });

lst.Add(new TDResponse { CenterID = "491", Center = "Enterprise", Yr = "2020", Mth = "JULY", Test_S = "0", Test_U = "35", Dev_S = "21", Dev_U = "0" });

lst.Add(new TDResponse { CenterID = "491", Center = "Enterprise", Yr = "2020", Mth = "JUNE", Test_S = "0", Test_U = "43", Dev_S = "34", Dev_U = "0" });

lst.Add(new TDResponse { CenterID = "491", Center = "Enterprise", Yr = "2020", Mth = "May", Test_S = "0", Test_U = "311", Dev_S = "55", Dev_U = "0" });

return lst;

}

 

Output needed

Test_S / Test_U to be coded as Test

Values of the both columns into SVal and UVal

 

CenterID | Center | Code | SVal | UVal | Yr | Mth

------+-------+-------+------+-------+-------------+-------+-------

491 | Enterprise | Test | 0    | 3       | 2020 | APRIL

491 | Enterprise | Test | 0    | 311   | 2020 | May

491 | Enterprise | Test | 0    | 43     | 2020 | JUNE

491 | Enterprise | Test | 0    | 35     | 2020 | JULY

491 | Enterprise | Dev | 45 | 0        | 2020 | APRIL

491 | Enterprise | Dev | 55 | 0        | 2020 | May

491 | Enterprise | Dev | 34 | 0        | 2020 | JUNE

491 | Enterprise | Dev | 21 | 0        | 2020 | JULY

Answer 1

This is OK carry on

Answer 2

I have tried using the Datatable . Please let me know , if this can rewritten better 

 

private static void Trail12()

{

List<TDResponse> lst = CreateDatSample();

DataTable table = new DataTable();

using (var reader = ObjectReader.Create(lst))

{

table.Load(reader);

}

var rpt = UnpivotDataTable(table);

}

public static DataTable UnpivotDataTable(DataTable pivoted)

{

string[] columnNames = pivoted.Columns.Cast<DataColumn>()

.Select(x => x.ColumnName)

.ToArray();

var unpivoted = new DataTable("unpivot");

unpivoted.Columns.Add(pivoted.Columns[0].ColumnName, pivoted.Columns[0].DataType);

unpivoted.Columns.Add(pivoted.Columns[1].ColumnName, pivoted.Columns[1].DataType);

unpivoted.Columns.Add(pivoted.Columns[2].ColumnName, pivoted.Columns[2].DataType);

unpivoted.Columns.Add(pivoted.Columns[3].ColumnName, pivoted.Columns[3].DataType);

unpivoted.Columns.Add("Code", typeof(string));

unpivoted.Columns.Add("Sval", typeof(string));

unpivoted.Columns.Add("UVal", typeof(string));

for (int r = 0; r < pivoted.Rows.Count; r++)

{

for (int c = 4; c < columnNames.Length; c++)

{

var value = pivoted.Rows[r][c]?.ToString();

if (!string.IsNullOrWhiteSpace(value))

{

DataRow dr = unpivoted.NewRow();

dr[0] = pivoted.Rows[r][0];

dr[1] = pivoted.Rows[r][1];

dr[2] = pivoted.Rows[r][2];

dr[3] = pivoted.Rows[r][3];

dr[4] = columnNames[c].Substring(0, columnNames[c].LastIndexOf('_'));

dr[5] = pivoted.Rows[r][c];

c = c + 1;

dr[6] = pivoted.Rows[r][c];

unpivoted.Rows.Add(dr);

}

}

}

return unpivoted;

}

Answer 3

Refer below articles. https://satvasolutions.com/pivot-and-unpivot-operator-in-sql-server-c-example-code/ https://www.c-sharpcorner.com/article/understanding-pivot-unpivot-and-reverse-pivot-statements/ https://www.c-sharpcorner.com/forums/pivot-and-unpivot-in-sql-server https://dotnettutorials.net/lesson/pivot-and-unpivot-sql-server/

Answer 4

Hi Kumar,

 

Please refer one good article on unpivot which will help you,

 

http://www.dotnetfunda.com/articles/show/3399/pivot-and-unpivot-in-csharp 

https://dotnetfiddle.net/PSkIDa 

https://stackoverflow.com/questions/42556927/linq-unpivot-a-list