Click here to Skip to main content
15,936,395 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have data in Rows like
DateTime              Status        EmpId

2018-05-28 8:00        01            2000347  

2018-05-28 20:18       02           2000347  

2018-05-28 8:00        01            2000348  

2018-05-28 17:18       02           2000348
Now i want above data convert into columns like this
EmpID               Status (1)INTime      Status(2)OutTime    

2000347            2018-05-28 8:00        2018-05-28 20:18

2000348            2018-05-28 8:00        2018-05-28 17:18

Date wise IN OUT pick against one employee ID this data exit in data base and transfer it into another table after converting into columns

Kindly help ..

What I have tried:

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Item"), new DataColumn("Quantity"), new DataColumn("Price") });
                dt.Rows.Add("Shirt", 145, 500);
                dt.Rows.Add("Jeans", 0, 2000);
                dt.Rows.Add("Trousers", 190, 1545);
                dt.Rows.Add("Tie", 30, 200);
                dt.Rows.Add("Cap", 0, 300);
                dt.Rows.Add("Hat", 90, 350);
                dt.Rows.Add("Scarf", 290, 140);
                dt.Rows.Add("Belt", 150, 400);
                ViewState["dt"] = dt;
                BindGrid(dt, false);
            }
        }

        private void BindGrid(DataTable dt, bool rotate)
        {
            GridView1.ShowHeader = !rotate;
            GridView1.DataSource = dt;
            GridView1.DataBind();
            if (rotate)
            {
                foreach (GridViewRow row in GridView1.Rows)
                {
                    row.Cells[0].CssClass = "header";
                }
            }
        }
                protected void Convert(object sender, EventArgs e)
{
    DataTable dt = (DataTable)ViewState["dt"];
    if ((sender as Button).CommandArgument == "1")
    {
        btnConvert1.Visible = false;
        btnConvert2.Visible = true;
        DataTable dt2 = new DataTable();
        for (int i = 0; i <= dt.Rows.Count; i++)
        {
            dt2.Columns.Add();
        }
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            dt2.Rows.Add();
            dt2.Rows[i][0] = dt.Columns[i].ColumnName;
        }
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                dt2.Rows[i][j + 1] = dt.Rows[j][i];
            }
        }
        BindGrid(dt2, true);
    }
    else
    {
        btnConvert1.Visible = true;
        btnConvert2.Visible = false;
        BindGrid(dt, false);
    }
Posted
Updated 18-Dec-18 1:35am
v2
Comments
ChauhanAjay 17-Dec-18 17:13pm    
Hope the below link helps.
https://www.codeproject.com/Articles/44274/%2FArticles%2F44274%2FTranspose-a-DataTable-using-C

1 solution

here is soultion

SELECT     empcode, MAX(CASE WHEN INOUT = 1 THEN Date END) AS INtime, MAX(CASE WHEN INOUT = 2 THEN Date END) AS Outtime
FROM         HR AS t
GROUP BY empcode, CONVERT(date, Date)
 
Share this answer
 
Comments
Richard Deeming 18-Dec-18 11:42am    
Presumably you don't have any overnight shifts? :)

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