How to convert rows in column datatable C#

Question

0.00/5 (No votes)

See more:

My Table Result is

Name	Id	fee_head	     fee
Jhon	        1	EXAM FEE              1
Jhon	        1	STATE OF MARKS	     80
Jhon	        1	TERM END EXAM	     300
Jhon	       1	COST OF FORM	      20
Charli	      40	EXAM FEE	      4
Charli	      40	STATE OF MARKS	      80
Charli	      40	TERM END EXAM	      300
Charli	       40	COST OF FORM	      20
Jacky	       2	EXAM FEE	      3
Jacky	       2	STATE OF MARKS	     80
Jacky	       2	TERM END EXAM	     300
Jacky	       2	COST OF FORM	     20

But I Want This Formate

Student_Name	Student_Id	fee_head_1	fee_1	fee_head_2	fee_2	fee_head_4	fee_4	fee_head_5	fee_5		
Jhon	1	EXAM FEE	1	STATE OF MARKS	80	TERM END EXAM	300	COST OF FORM	20		
Charli	40	EXAM FEE	1	STATE OF MARKS	80	TERM END EXAM	300	COST OF FORM	20		
Jacky	2	EXAM FEE	1	STATE OF MARKS	80	TERM END EXAM	300	COST OF FORM	20

First tow column is as it is...and fee_head column is multiple and rwos formate how do???plzz Help

What I have tried:

i have some code try but

DataTable query = new DataTable();
  query = obj_Cls_Main.Read_Table("RESULT QUERY");

             DataTable dtpivot = new DataTable();
             for (int i = 0; i <= query.Rows.Count; i++)
             {
                 dtpivot.Columns.Add(i.ToString());

             }
             for (int i = 0; i < query.Columns.Count; i++)

             {

                 DataRow dr = dtpivot.NewRow();

                 dr[0] = query.Columns[i].ToString();

                 for (int j = 0; j < query.Rows.Count; j++)

                     dr[j + 1] = query.Rows[j][i];

                 dtpivot.Rows.Add(dr);

             }
             gridControl1.DataSource = dtpivot;

Posted 21-Jul-17 3:14am

RizwanShaikh

Updated 21-Jul-17 6:31am

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)

Sheila Pontes · Answer 1 · 2017-07-21T06:31:00

Hi,

Below, the code that sorts the table.

C#

private int find_index_column_list(string[,] list, string element)
{
    int index_column = 0;
    for (int index = 0; index < (list.Length /2); index++)
    {
        if (list[index, 0] == element)
        {
            index_column = Convert.ToInt16(list[index, 1]);
            return index_column;
        }
    }

    return -1;
}

private void order_table()
{
    try
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Name");
        dt.Columns.Add("Id");
        dt.Columns.Add("fee_head");
        dt.Columns.Add("fee");

        DataRow dr = dt.NewRow();
        dr["Name"] =  "John";
        dr["Id"] = "1";
        dr["fee_head"] = "EXAM FEE";
        dr["fee"] = "1";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["Name"] =  "John";
        dr["Id"] = "1";
        dr["fee_head"] = "STATE OF MARKS";
        dr["fee"] = "80";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["Name"] =  "Charli";
        dr["Id"] = "40";
        dr["fee_head"] = "EXAM FEE";
        dr["fee"] = "1";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["Name"] =  "Charli";
        dr["Id"] = "40";
        dr["fee_head"] = "STATE OF MARKS";
        dr["fee"] = "80";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["Name"] =  "Jacky";
        dr["Id"] = "2";
        dr["fee_head"] = "EXAM FEE";
        dr["fee"] = "1";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["Name"] =  "Jacky";
        dr["Id"] = "2";
        dr["fee_head"] = "STATE OF MARKS";
        dr["fee"] = "80";
        dt.Rows.Add(dr);

        //create columns
        DataTable dtpivot = new DataTable();
        dtpivot.Columns.Add("Student_Name");
        dtpivot.Columns.Add("Student_Id");
        dtpivot.Columns.Add("fee_head_1");
        dtpivot.Columns.Add("fee_1");
        dtpivot.Columns.Add("fee_head_2");
        dtpivot.Columns.Add("fee_2");


        string[,] list = new string[2, 2];
        list[0, 0] = "1"; //fee id
        list[0, 1] = "3"; //index column
        list[1, 0] = "80";//fee id
        list[1, 1] = "5"; //index column

        string student_id = "";
        int index_column = 0; //index column element
        dr = null;

        //read each line
        foreach (DataRow drline in dt.Rows )
        {
            //verify if is the same student
            if (student_id != drline["Id"].ToString())
            {
                //verify first record
                if (dr != null) { dtpivot.Rows.Add(dr); }

                //inicialize the line
                student_id = drline["Id"].ToString();
                dr = dtpivot.NewRow();
                dr["Student_Name"] = drline["Name"].ToString();
                dr["Student_Id"] = drline["Id"].ToString();
            }

            //find columns for data - head and id
            index_column = this.find_index_column_list(list, drline["fee"].ToString());
            dr[index_column] = drline["fee"].ToString();
            dr[(index_column - 1)] = drline["fee_head"].ToString();
        }

        //verify last record
        if (dr != null) { dtpivot.Rows.Add(dr); }

    }
    catch (Exception ex)
    {
        //message error.
    }
}