Trying to convert rows to columns using c#

Question

User-471420332 posted

Below is my code which I am using

private DataTable GenerateTransposedTable(DataTable inputTable)
{
    DataTable outputTable = new DataTable();
    // Add columns by looping rows
    // Header row's first column is same as in inputTable
    outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());
    // Header row's second column onwards, 'inputTable's first column taken
    foreach (DataRow inRow in inputTable.Rows)
    {
        string newColName = inRow[0].ToString();
        DataColumnCollection columns = outputTable.Columns;
        if (!columns.Contains(newColName))//code to check duplication of columns
        {
            outputTable.Columns.Add(newColName);
        }
    }
    // Add rows by looping columns        
    for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
    {
        DataRow newRow = outputTable.NewRow();
        // First column is inputTable's Header row's second column
        newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
        for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
        {
            string colValue = inputTable.Rows[cCount][rCount].ToString();
            //int rowcount = cCount + 1;
            //if (cCount <= rowcount)
            //{
            newRow[cCount + 1] = colValue; //**Error here when starts second record**
            //}
        }
        outputTable.Rows.Add(newRow);
    }

    //for (int i = outputTable.Rows.Count - 1; i >= 0; i--)
        for (int j = 0; j < outputTable.Columns.Count;j++)
        {
            for (int k = 0; k < outputTable.Rows.Count; k++)
            {
                string sdfsdf = outputTable.Rows[k][j].ToString();
                if (outputTable.Rows[k][j].ToString() == "0")
                {
                    DataRow dr = outputTable.Rows[k];
                    dr.Delete();
                }

            }

        }
        outputTable.AcceptChanges();
    return outputTable;
} 

Below is the format data iam getting from xml which iam converting to dataTable as below

 dataTable
 **Columnname               value**
 CANDIDATEID                3215456454454
 Last Name                  demo1
 Email                      [e-mail]
 Mobile                     0000000
 Experience in Years        null
 Current Employer           null
 CANDIDATEID                2515456564
 Last Name                  demo2
 Email                      [e-mail]
 Mobile                     354564654
 Experience in Years        null
 Current Employer           null

I need output like below :column names(for sample I specified only some columns here i need all the columns which i am getting in datatable )

 CandidateId           Last Name                Email              Mobile  etc
 3215456454454           demo1                 [e-mail]      00000000
 2515456564              demo2                  [e-mail]       354564654

I am waiting for your valuable reply and thank you in advance for your effort and help in asp.net forum.

Answer 1

User1992938117 posted

See the sample working code:

public partial class Contact : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //Assuming your data is as below DataTable
            DataTable inputTable = GetTable();
            
            //Get Transposed DataTable with header names as Column_0, Column_1 etc..
            DataTable transposedTable = GenerateTransposedTable(inputTable);

            //Bind to grid for sampling
            GridView1.DataSource = transposedTable;
            GridView1.DataBind();
        }
        
        private DataTable GenerateTransposedTable(DataTable inputTable)
        {
            DataTable outputTable = new DataTable();

            // Add columns by prefixing Column_ by looping rows
            for (int i = 0; i <= inputTable.Rows.Count; i++)
            {
                outputTable.Columns.Add("Column_" + i.ToString());
            }

            // Add rows by looping columns        
            for (int rCount = 0; rCount <= inputTable.Columns.Count - 1; rCount++)
            {
                DataRow newRow = outputTable.NewRow();

                // First column is inputTable's Header row's second column
                newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
                for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
                {
                    string colValue = inputTable.Rows[cCount][rCount].ToString();
                    newRow[cCount + 1] = colValue;
                }
                outputTable.Rows.Add(newRow);
            }

            return outputTable;
        }

        /// <summary>
        /// To get sample data in Datatable
        /// </summary>
        /// <returns></returns>
        private static DataTable GetTable()
        {
            // Here we create a DataTable with four columns.
            DataTable table = new DataTable();
            table.Columns.Add("CANDIDATEID", typeof(Int64));
            table.Columns.Add("Last Name", typeof(string));
            table.Columns.Add("Email", typeof(string));
            table.Columns.Add("Mobile", typeof(string));
            table.Columns.Add("Experience in Years", typeof(Int32));
            table.Columns.Add("Current Employer", typeof(string));

            // Here we add five DataRows.
            table.Rows.Add(3215456454454, "demo1", "[e-mail]", "0000000", null, null);
            table.Rows.Add(2515456564, "demo2", "[e-mail]", "354564654", null, null);
            return table;
        }
    }

Credit: https://www.codeproject.com/Articles/44274/Transpose-a-DataTable-using-C 

Answer 2

User-1171043462 posted

Refer

Rotate DataTable - Convert DataTable Columns to Rows and Rows to Columns using C# and VB.Net

Answer 3

User-471420332 posted

Thanks for the replay but in my case i have dataTable with multiple records in the sample(which you have given https://www.aspsnippets.com/Articles/Rotate-DataTable---Convert-DataTable-Columns-to-Rows-and-Rows-to-Columns-using-C-and-VBNet.aspx)  we have only single records

please help to solve the issue i need to bind rows to columns having multiple rows means I get candidate details of 2 persons in

horizontal

Candidate Id 5435656

Name   demo

email [e-mail]

Candidate Id  5435657

Name demo2

email [e-mail]

now I need this below format

candidateId    name   email

5435656         demo   [e-mail]

5435657         demo2   [e-mail]

Your sample has only single rows converting to columns I tried with the below code

private DataTable GenerateTransposedTable(DataTable inputTable)
{
DataTable outputTable = new DataTable();
// Add columns by looping rows
// Header row's first column is same as in inputTable
outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());
// Header row's second column onwards, 'inputTable's first column taken
foreach (DataRow inRow in inputTable.Rows)
{
string newColName = inRow[0].ToString();
DataColumnCollection columns = outputTable.Columns;
if (!columns.Contains(newColName))//code to check duplication of columns
{
outputTable.Columns.Add(newColName);
}
}
// Add rows by looping columns
for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
{
DataRow newRow = outputTable.NewRow();
// First column is inputTable's Header row's second column
newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
{
string colValue = inputTable.Rows[cCount][rCount].ToString();
int colmcount = outputTable.Columns.Count;

newRow[cCount + 1] = colValue;//error comes here when second record starts loop columns does not belong
}
outputTable.Rows.Add(newRow);
}
outputTable.AcceptChanges();
return outputTable;
}

Please do replay Waiting worked on other samples like linq but It does not full fill my requrimenet I get 2 different tables if I use Linq but i need the below format in single table 

candidateId    name   email

5435656         demo   [e-mail]

5435657         demo2   [e-mail]

Answer 4

User1992938117 posted

Have you tried this one !

If you need Herder names fixed and you know the names then add at below line

 // Add columns by prefixing Column_ by looping rows
            for (int i = 0; i <= inputTable.Rows.Count; i++)
            {
                outputTable.Columns.Add("Column_" + i.ToString());
            }

Add columns one by one

outputTable.Columns.Add("candidateId");
outputTable.Columns.Add("name");
outputTable.Columns.Add("email");

Answer 5

User-471420332 posted

Iam getting table as below 

val	CANDIDATEID	Candidate ID	Last Name	Mobile	CANDIDATEID	Candidate ID	Last Name	Mobile
FL_Text	3265655	245048	demo1	9554441515	3254500	7179	demo2	9985633344
row_Id	0	0	0	0	1	1	1	1

But I need the second record in the second row code I tried

private DataTable GenerateTransposedTable(DataTable inputTable)
{
DataTable outputTable = new DataTable();
// Add columns by prefixing Column_ by looping rows
for (int i = 0; i <= inputTable.Rows.Count; i++)
{
outputTable.Columns.Add("Column_" + i.ToString());
}
//outputTable.Columns.Add("Candidate ID");
//outputTable.Columns.Add("Last Name");
//outputTable.Columns.Add("Mobile");
//outputTable.Columns.Add("Email");
// Add rows by looping columns

// for (int i = 0; i < inputTable.Columns.Count; i++)
//{
////for (int i = 0; i < inputTable.Columns.Count; i++)
////{
// outputTable.Rows.Add();
// outputTable.Rows[i][0] = inputTable.Columns[i].ColumnName;
//}

//for (int i = 0; i < inputTable.Columns.Count-1; i++)
//{
// //DataRow newRow = outputTable.NewRow();
// for (int j = 0; j < inputTable.Rows.Count-1; j++)
// {
// if (i == 1)
// {
// string colValue = inputTable.Rows[j][i].ToString();
// //newRow[j + 1] = colValue;
// //outputTable.Rows[]
// for (int k = 0; k < outputTable.Columns.Count-1; k++)
// {
// //newRow[k + 1] = colValue;
// outputTable.Rows[k][j+1]=inputTable.Rows[j][i];
// }
// }
// }
//}
for (int rCount = 0; rCount <= inputTable.Columns.Count - 1; rCount++)
{
DataRow newRow = outputTable.NewRow();

// First column is inputTable's Header row's second column
newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
{
string colValue = inputTable.Rows[cCount][rCount].ToString();
newRow[cCount + 1] = colValue;
}
outputTable.Rows.Add(newRow);
}
return outputTable;
}

Help me to solve the issue I had been struggling from past 3 days were I went wrong ??

Answer 6

User1992938117 posted

Please try working code

public partial class Contact : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //Assuming your data is as below DataTable
            DataTable inputTable = GetTable();

            //Get Transposed DataTable with header names as Column_0, Column_1 etc..
            DataTable transposedTable = GenerateTransposedTable(inputTable);

            //Bind to grid for sampling
            GridView1.DataSource = transposedTable;
            GridView1.DataBind();
        }

        private DataTable GenerateTransposedTable(DataTable inputTable)
        {
            DataTable outputTable = new DataTable();

            // Add columns in output table
            outputTable.Columns.Add("Candidate ID");
            outputTable.Columns.Add("Last Name");
            outputTable.Columns.Add("Mobile");
            outputTable.Columns.Add("Email");

            for (int tRowIndex = 0; tRowIndex <= inputTable.Rows.Count - 1;)
            {
                DataRow newRow = outputTable.NewRow();
                outputTable.Rows.Add(new object[]
                {
                    inputTable.Rows[tRowIndex][1], //to add value in Candidate ID column
                    inputTable.Rows[tRowIndex + 1][1], //to add value in Last Name column
                    inputTable.Rows[tRowIndex + 2][1], //to add value in Mobile column
                    inputTable.Rows[tRowIndex + 3][1] //to add value in Email column
                });

                tRowIndex = tRowIndex + 4; //Count of columns
            }

            return outputTable;
        }

        /// <summary>
        /// To get sample data in Datatable
        /// </summary>
        /// <returns></returns>
        private static DataTable GetTable()
        {
            // Here we create a DataTable with four columns.
            DataTable table = new DataTable();
            table.Columns.Add("ColumnName", typeof(string));
            table.Columns.Add("Value", typeof(string));

            // Here we add 4 DataRows for first set of data
            table.Rows.Add("CANDIDATEID", "3215456454454");
            table.Rows.Add("Last Name", "demo1");
            table.Rows.Add("Email", "[e-mail]");
            table.Rows.Add("Mobile", "0000000");

            // Here we add 4 DataRows for next set of data
            table.Rows.Add("CANDIDATEID", "234567");
            table.Rows.Add("Last Name", "demo2");
            table.Rows.Add("Email", "[e-mail]");
            table.Rows.Add("Mobile", "0000233000");
          
            return table;
        }
    }