There's few ways to achieve that:
#1 - using proper Selet statement:
SELECT *
FROM (
SELECT Location, [Item Type], AmountA As Amount, "A" AS [Type] FROM [Sheet#1]
UNION ALL
SELECT Location, [Item Type], AmountB As Amount, "B" AS [Type] FROM [Sheet#1]
);
#2 - using
foreach
loop:
foreach(DataRow data in dt.Rows)
{
foreach(string col in new string[]{"AmountA", "AmountB"})
{
DataRow dr = destdt.NewRow();
dr["Location"] = data["Location"];
dr["Item Type"] = data["Item Type"];
dr["Amount"] = data[col];
dr["Type"] = col.Substring(col.Length-1, 1);
destdt.Rows.Add(dr);
}
}
#3 - by using Linq
Union[
^] method +
LoadDataRow[
^] method to "convert" anonymous data type into
DataRow[
^].
Note: this solution is very similar to #1, but the main difference is that that below code is executed on client side (in memory) instead of database level.
See:
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn("Location", typeof(string)),
new DataColumn("Item Type", typeof(string)),
new DataColumn("AmountA", typeof(int)),
new DataColumn("AmountB", typeof(int)),
new DataColumn("Type", typeof(string))
});
dt.Rows.Add(new object[]{"A", "A", 5, 4, null});
DataTable destdt = new DataTable();
destdt.Columns.AddRange(new DataColumn[]
{
new DataColumn("Location", typeof(string)),
new DataColumn("Item Type", typeof(string)),
new DataColumn("Amount", typeof(int)),
new DataColumn("Type", typeof(string))
});
destdt = dt.AsEnumerable()
.Select(x=> new
{
Location = x.Field<string>("Location"),
ItemType = x.Field<string>("Item Type"),
Amount = x.Field<int>("AmountA"),
Type = "A",
})
.Union(dt.AsEnumerable()
.Select(x=> new
{
Location = x.Field<string>("Location"),
ItemType = x.Field<string>("Item Type"),
Amount = x.Field<int>("AmountB"),
Type = "B",
}))
.Select(x=>destdt.LoadDataRow(new object[]
{
x.Location,
x.ItemType,
x.Amount,
x.Type
}
, false))
.CopyToDataTable();