Click here to Skip to main content
15,851,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I am facing one problem in transposing multiple columns into a single column with multiple rows. Below is the problem structure and output expected in C#.

Input DataTable

PersonId Code 1 Code 2 Code 3 Text 1 Text 2 Text 3 LOC 1 LOC 2 LOC 3
1 CD1 CD2 CD3 TE1 TE2 TE3 LO1 LO2 LO3


OUTPUT DataTable

PersonId Code Text LOC
1 CD1 TE1 LO1
1 CD2 TE2 LO2
1 CD3 TE3 LO3

I couldn't find any article which is giving solution for similar problem.

What I have tried:

I tried using for loop but for loops are getting nested.

Inside one for loop there are 3 for loops for 3 columns.

Can we make some generic method ?
Posted
Updated 11-Mar-16 4:56am

1 solution

It's possible to do this with either a single loop (which then would require a small amount of code repetition) or two nested loops:

C#
DataTable outputTable = inputTable.Clone(); // copies the schema (columns)
// todo: remove the columns Code2, Code3, Text2, etc. from outputTable
foreach(DataRow inputRow in inputTable.Rows)
{
    for (int i=0; i<3; i++)
    {
        DataRow newRow = outputTable.NewRow();
        newRow[0] = inputRow[0];
        newRow[1] = inputRow[1 + i];
        newRow[2] = inputRow[4 + i];
        newRow[3] = inputRow[7 + i];
        outputTable.Rows.Add(newRow);
    }
}
 
Share this answer
 
Comments
Member 12385501 12-Mar-16 8:33am    
Hi Sascha,

Thanks a lot for the solution which you have provided. It really helped.

Can we make the above solution a bit generic method in terms like if I am keeping the first column and transposing the repeated columns into distinct columns. I know my number of repetitions for other columns will be same. like below example

Case 1:

Input DataTable

PersonId Code 1 Code 2 Code 3 Text 1 Text 2 Text 3 LOC 1 LOC 2 LOC 3
1 CD1 CD2 CD3 TE1 TE2 TE3 LO1 LO2 LO3


OUTPUT DataTable

PersonId Code Text LOC
1 CD1 TE1 LO1
1 CD2 TE2 LO2
1 CD3 TE3 LO3

Case 2
Input Table

PersonId Job1 Job2 Job3 Job4 Job5
1 1 2 3 4 5

Output table

PersonId Job
1 1
1 2
1 3
1 4
1 5

Similarly, there can be other tables as well with multiple repeated columns but no of repetitions is same in a given table.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900