How to transpose multiple columns into one column

Question

0.00/5 (No votes)

See more:

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 11-Mar-16 3:00am

Member 12385501

Updated 11-Mar-16 3:56am

Add a Solution

1 solution

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)

Sascha Lefèvre · Accepted Answer · 2016-03-11T03:56:00

Solution 1

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);
    }
}

Posted 11-Mar-16 3:56am

Sascha Lefèvre

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.