Unpivot multiple columns

Kumar S

6y2.8k

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 CreateDatSample()

{

List lst = new List();

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

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

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar6y ago
This is OK carry on
Kumar S6y ago
I have tried using the Datatable . Please let me know , if this can rewritten better

private static void Trail12()
{
List 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()
.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;
}
Rajanikant Hawaldar6y ago
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/
Jignesh Kumar6y ago
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