locked
Transpose a DataTable from Rows to Column RRS feed

  • Question

  • User1324761900 posted

    Hi Team,

    Please share c# code for below example, very urgent.

    Input Data Table:

    DATE

    PRODUCT

    BRANCH

    QTY

    02150301

    A

    CHENNAI

    1

    02150301

    B

    CHENNAI

    2

    02150302

    A

    CHENNAI

    50

    02150302

    B

    CHENNAI

    60

    02150303

    A

    CHENNAI

    80

    02150303

    B

    CHENNAI

    90

     

    Output Data Table:

    PRODUCT

    02150301

    02150302

    02150303

    BRANCH

    A

    1

    50

    80

    CHENNAI

    B

    2

    60

    90

    CHENNAI

    Thursday, March 12, 2015 11:12 AM

Answers

  • User-1103938767 posted
    public static DataTable GetInversedDataTable(DataTable table, string columnX, string columnY, string columnZ)
            {
                //Create a DataTable to Return
                DataTable returnTable = new DataTable();
    
                //Add a Column at the beginning of the table
                returnTable.Columns.Add(columnY);
    
                //Read all DISTINCT values from columnX Column in the provided DataTale
                List<string> columnXValues = new List<string>();
    
                foreach (DataRow dr in table.Rows)
                {
                    string columnXTemp = dr[columnX].ToString();
                    if (!columnXValues.Contains(columnXTemp))
                    {
                        //Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.
                        columnXValues.Add(columnXTemp);
                        returnTable.Columns.Add(columnXTemp);
                    }
                }
    
                //Verify if Y and Z Axis columns re provided
                if (columnY != "" && columnZ != "")
                {
                    //Read DISTINCT Values for Y Axis Column
                    List<string> columnYValues = new List<string>();
    
                    foreach (DataRow dr in table.Rows)
                    {
                        if (!columnYValues.Contains(dr[columnY].ToString()))
                            columnYValues.Add(dr[columnY].ToString());
                    }
    
                    //Loop all Column Y Distinct Value
                    foreach (string columnYValue in columnYValues)
                    {
                        //Creates a new Row
                        DataRow drReturn = returnTable.NewRow();
                        drReturn[0] = columnYValue;
                        //foreach column Y value, The rows are selected distincted
                        DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");
    
                        //Read each row to fill the DataTable
                        foreach (DataRow dr in rows)
                        {
                            string rowColumnTitle = dr[columnX].ToString();
    
                            //Read each column to fill the DataTable
                            foreach (DataColumn dc in returnTable.Columns)
                            {
                                if (dc.ColumnName == rowColumnTitle)
                                {
                                    drReturn[rowColumnTitle] = dr[columnZ];
                                }
                            }
                        }
                        returnTable.Rows.Add(drReturn);
                    }
                }
                else
                {
                    throw new Exception("The columns to perform inversion are not provided");
                }
                return returnTable;
            }

    You can use this method for transforming your data table. You need to pass following values to parameters

    table: Your Datatable

    columnX: "DATE"

    columnY: "PRODUCT"

    columnZ: "QTY"

    This is a generic method, you can use it for any data table. You just need to pass columns value as parameter. You can also customize this method only specific to this data table if you want to add "BRANCH" as last column. 

    Please let me know if it solves your problem.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 13, 2015 1:20 AM
  • User-271186128 posted

    Hi vinovasu,

    As indicated in my provious reply, the previous code just apply to the table in your earlier post.

    As for the dynamic dates, do you mean the new datatable columns like this:

    Columns: PRODUCT 02150301 02150302 02150303 02150304 … BRANCH

    As for this scenario, I suppose you need to dynamic create the new datatable with
    apove columns. Here are some samples, you could refer to them.
    http://www.codeproject.com/Articles/44274/Transpose-a-DataTable-using-C
    http://forums.asp.net/t/1822478.aspx?Convert+DataTable+From+Rows+To+Columns 

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, March 15, 2015 8:21 PM

All replies

  • User-1103938767 posted

    Why dont you do it by sql, it is so easy in sql. In Sql server, you can do it by PIVOT.

    Thursday, March 12, 2015 12:39 PM
  • User1324761900 posted

    Sorry. i need only for c# code. so please kindly share the code

    Thursday, March 12, 2015 10:10 PM
  • User-1103938767 posted
    public static DataTable GetInversedDataTable(DataTable table, string columnX, string columnY, string columnZ)
            {
                //Create a DataTable to Return
                DataTable returnTable = new DataTable();
    
                //Add a Column at the beginning of the table
                returnTable.Columns.Add(columnY);
    
                //Read all DISTINCT values from columnX Column in the provided DataTale
                List<string> columnXValues = new List<string>();
    
                foreach (DataRow dr in table.Rows)
                {
                    string columnXTemp = dr[columnX].ToString();
                    if (!columnXValues.Contains(columnXTemp))
                    {
                        //Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.
                        columnXValues.Add(columnXTemp);
                        returnTable.Columns.Add(columnXTemp);
                    }
                }
    
                //Verify if Y and Z Axis columns re provided
                if (columnY != "" && columnZ != "")
                {
                    //Read DISTINCT Values for Y Axis Column
                    List<string> columnYValues = new List<string>();
    
                    foreach (DataRow dr in table.Rows)
                    {
                        if (!columnYValues.Contains(dr[columnY].ToString()))
                            columnYValues.Add(dr[columnY].ToString());
                    }
    
                    //Loop all Column Y Distinct Value
                    foreach (string columnYValue in columnYValues)
                    {
                        //Creates a new Row
                        DataRow drReturn = returnTable.NewRow();
                        drReturn[0] = columnYValue;
                        //foreach column Y value, The rows are selected distincted
                        DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");
    
                        //Read each row to fill the DataTable
                        foreach (DataRow dr in rows)
                        {
                            string rowColumnTitle = dr[columnX].ToString();
    
                            //Read each column to fill the DataTable
                            foreach (DataColumn dc in returnTable.Columns)
                            {
                                if (dc.ColumnName == rowColumnTitle)
                                {
                                    drReturn[rowColumnTitle] = dr[columnZ];
                                }
                            }
                        }
                        returnTable.Rows.Add(drReturn);
                    }
                }
                else
                {
                    throw new Exception("The columns to perform inversion are not provided");
                }
                return returnTable;
            }

    You can use this method for transforming your data table. You need to pass following values to parameters

    table: Your Datatable

    columnX: "DATE"

    columnY: "PRODUCT"

    columnZ: "QTY"

    This is a generic method, you can use it for any data table. You just need to pass columns value as parameter. You can also customize this method only specific to this data table if you want to add "BRANCH" as last column. 

    Please let me know if it solves your problem.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 13, 2015 1:20 AM
  • User-271186128 posted

    Hi vinovasu,

    As for this issue, I suppose you need to create a new DataTable with the following columns and loop through the original DataTable.

    Columns: PRODUCT 02150301 02150302 02150303 BRANCH<!--?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" /--><o:p></o:p>

    You could refer to the following code:

                //Create new table
                DataTable Newdt = new DataTable();
                Newdt.Columns.AddRange(new DataColumn[5] { new DataColumn("PRODUCT"), new DataColumn("02150301"), new DataColumn("02150302"), new DataColumn("02150303"), new DataColumn("BRANCH") });
    
                ViewState["newTable"] = Newdt;
    
                var pp = (from ee in dt.AsEnumerable()
                          select ee.Field<string>("PRODUCT")).Distinct();
    
                foreach (var item in pp)
                {
                    InsertValueToTable(dt, item);
                }
    
                GridView2.DataSource = (DataTable)ViewState["newTable"];
                GridView2.DataBind();
    
            private void InsertValueToTable(DataTable dt, string parm)
            {
                var query = from cc in dt.AsEnumerable()
                            where cc.Field<string>("PRODUCT") == parm
                            select new
                            {
                                Date = cc.Field<string>("DATE"),
                                Branch = cc.Field<string>("BRANCH"),
                                Qty = cc.Field<string>("QTY"),
                            };
    
                string s1 = "";
                string s2 = "";
                string s3 = "";
                foreach (var item in query)
                {
                    if (item.Date == "02150301")
                    {
                        s1 = item.Qty;
                    }
                    else if (item.Date == "02150302")
                    {
                        s2 = item.Qty;
                    }
                    else if (item.Date == "02150303")
                    {
                        s3 = item.Qty;
                    }
                }
                DataTable newDT = (DataTable)ViewState["newTable"];
                newDT.Rows.Add(parm, s1, s2, s3, "CHENNAI");
            }

    The output:

    Note: The above code just apply to the table in your post.

    Best Regards,
    Dillion

    Friday, March 13, 2015 2:33 AM
  • User1324761900 posted

    Thanks for your response Zhi LV, but i'm using dynamic dates. so how to add dynamic dates into "Newdt" Datatable.

    Saturday, March 14, 2015 10:23 AM
  • User-271186128 posted

    Hi vinovasu,

    As indicated in my provious reply, the previous code just apply to the table in your earlier post.

    As for the dynamic dates, do you mean the new datatable columns like this:

    Columns: PRODUCT 02150301 02150302 02150303 02150304 … BRANCH

    As for this scenario, I suppose you need to dynamic create the new datatable with
    apove columns. Here are some samples, you could refer to them.
    http://www.codeproject.com/Articles/44274/Transpose-a-DataTable-using-C
    http://forums.asp.net/t/1822478.aspx?Convert+DataTable+From+Rows+To+Columns 

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, March 15, 2015 8:21 PM
  • User1324761900 posted

    Thanks Guys

    Tuesday, March 17, 2015 3:33 AM