How to generate pivot output from my datatable

[Sudip_inn]

i read this link Pivot C# Array or DataTable: Convert a Column To a Row with LINQ - TechBrij but their code did not help my situation

i am reading data from xml file into datatable. xml looks like

<?xml version="1.0" encoding="utf-8"?>
<TickerBrokerStandardDateLineitem>
<TickerBrokerStandardDateLineitemValues>
<TickerBrokerStandardDateLineitemValue>
<TabName>Consensus Model</TabName>
<StandardDate>2010 FY</StandardDate>
<XFundCode>TRIN</XFundCode>
<BRTab></BRTab>
<BRLineItem></BRLineItem>
<StandardLineItem>Net Revenue</StandardLineItem>
<StandardValue>1608.7</StandardValue>
<ActualProvidedByCompany>NO</ActualProvidedByCompany>
</TickerBrokerStandardDateLineitemValue>
<TickerBrokerStandardDateLineitemValue>
<TabName>Consensus Model</TabName>
<StandardDate>2011 FY</StandardDate>
<XFundCode>TRIN</XFundCode>
<BRTab></BRTab>
<BRLineItem></BRLineItem>
<StandardLineItem>Net Revenue</StandardLineItem>
<StandardValue>1429.1</StandardValue>
<ActualProvidedByCompany>YES</ActualProvidedByCompany>
</TickerBrokerStandardDateLineitemValue>
</TickerBrokerStandardDateLineitem>

private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dtmyDataTable = new DataTable();
ds.ReadXml(@"C:\RDSS WorkBench_Stage\DBSource\TER\TER~10QK~Bogey.xml");

var data2 = dtmyDataTable.AsEnumerable().Select(x => new
{
TabName = x.Field<String>("TabName"),
StandardLineItem = x.Field<String>("StandardLineItem"),
XFundCode = x.Field<String>("XFundCode"),
StandardDate = x.Field<int>("StandardDate"),
StandardValue = x.Field<int>("StandardValue")
});

DataTable pivotDataTable = data2.ToPivotTable(
item => item.TabName,
item => item.StandardLineItem,
item => item.XFundCode,
item => item.StandardDate,
items => items.Any() ? items.Sum(x => x.StandardValue) : 0);
}

above code throwing error!! data2.ToPivotTable() function does not take so many argument which throwing error

public static class Utility
{
public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
this IEnumerable<T> source,
Func<T, TColumn> columnSelector,
Expression<Func<T, TRow>> rowSelector,
Func<IEnumerable<T>, TData> dataSelector)
{
DataTable table = new DataTable();
var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
table.Columns.Add(new DataColumn(rowName));
var columns = source.Select(columnSelector).Distinct();

foreach (var column in columns)
table.Columns.Add(new DataColumn(column.ToString()));

var rows = source.GroupBy(rowSelector.Compile())
.Select(rowGroup => new
{
Key = rowGroup.Key,
Values = columns.GroupJoin(
rowGroup,
c => c,
r => columnSelector(r),
(c, columnGroup) => dataSelector(columnGroup))
});

foreach (var row in rows)
{
var dataRow = table.NewRow();
var items = row.Values.Cast<object>().ToList();
items.Insert(0, row.Key);
dataRow.ItemArray = items.ToArray();
table.Rows.Add(dataRow);
}

return table;
}
}


output would looks like




in my xml there is TabName which would be Section

StandardLineItem would LineItem

XFundCode would be XFundCode

StandardDate would come at header text will be pivot type output

StandardValue will be come as value

Basically TabName, StandardLineItem and XFundCode will be fixed column.

just StandardDate need to be pivot like with value.

in my case a each row would be uniquely identify in xml as TabName,StandardLineItem and StandardDate

now tell me how could i generate a generic rutine where i will send my datatable and which return data in above format for which i have attached a screen shot.

please help me with sample code. thanks

Continue reading...