Filter pivoted datatable

Question

1.00/5 (1 vote)

See more:

Using C# for visual studio 2008 (desktop) I'd like to convert a datatable as shown below. I got the pivoting code from: Pivoting DataTable Simplified[^]

This is what it is

Code	Period	Vendor		Numeric	FileDate	20100804 	20101201	20110604	
USCPIAP	2010	ABN AMRO	2010	01/06/2011					-
USCPIAP	2010	ABN AMRO	2010	01/12/2010			1.6	
USCPIAP	2010	ABN AMRO	2010	04/08/2010	1.5		
USCPIAP	2011	ABN AMRO	2011	01/06/2011					3.0
USCPIAP	2011	ABN AMRO	2011	01/12/2010			1.6	
USCPIAP	2011	ABN AMRO	2011	04/08/2010	1.6		
USCPIAP	2012	ABN AMRO	2012	01/06/2011					2.1
USCPIAP	2012	ABN AMRO	2012	01/12/2010			-

This is what should be

Code	Period	Vendor		Numeric	FileDate	20100804 	20101201	20110604	
USCPIAP	2010	ABN AMRO	2010	01/06/2011	1.5		1.6		-
USCPIAP	2011	ABN AMRO	2011	01/06/2011	1.6		1.6		3.0
USCPIAP	2012	ABN AMRO	2012	01/06/2011			-	         2.1

Posted 24-Jul-13 4:38am

Alhal

Updated 24-Jul-13 5:26am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 24-Jul-13 11:33am

Are you shure you want to this in C# code? I would prefer to use T-SQL.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Answer 1 · 2013-07-24T04:47:00

Solution 1

Have you read my Article on CodeProject about pivotting?
You can find it here[^].

Posted 24-Jul-13 4:47am

Herman<T>.Instance

Comments

Alhal 25-Jul-13 9:16am

I'm not sure how it helps. I'm using C# not SQL. I'm also not clear on why a dynamic pivot would meet my need. Please could you elaborate?

Herman<T>.Instance 26-Jul-13 4:37am

do the samples in the article in you'll find out. If you create a view or stored procedure you can still get the data in your c# code

Alhal 26-Jul-13 4:44am

Here is the code I have so far.

dt4PivotedAndSorted is what I have.
dt4PivotedAndSortedAndCompressed is what I want

using (DataTable dt4PivotedAndSortedAndCompressed = new DataTable())
{
foreach (DataColumn dc2 in dt4PivotedAndSorted.Columns)
{
dt4PivotedAndSortedAndCompressed.Columns.Add(dc2.ColumnName);
}
Rowct = 0;
DataRow dr6 = dt4PivotedAndSortedAndCompressed.NewRow();
foreach (DataRow dr5 in dt4PivotedAndSorted.Rows)
{
try
{
Colct = 0;

foreach (DataColumn dc3 in dt4PivotedAndSorted.Columns)
{
if (dr5[Colct].ToString ()!="")
{

dr6[Colct] = dr5[Colct];
}

Colct += 1;
}
if (dr5["Period"] != dr6["Period"])
{
dt4PivotedAndSortedAndCompressed.Rows.Add(dr6);
//DataRow dr6 = dt4PivotedAndSortedAndCompressed.NewRow();

}
}

catch (Exception ex)
{
lblErrs.Text = ex.Message;
}

}
}

Alhal · Answer 2 · 2013-07-26T04:19:00

Solution 3

I found the answer. Once I'd created a numeric version of the filedate for the pivot I should have removed the filedate column.

Posted 26-Jul-13 4:19am

Alhal