根据条件将表中的选定值转换为另一个表

问题描述

嗨..



我有一张名为Hikes的桌子。好吧，数据插入到.aspx页面的excel表中..



列如下：





EmpID名称1月11日 - 2月11日 - 11月11日



等等它继续..



它有3年的数据..



现在我有.aspx页面，我可以给empid ..在进入empid时，基于它我需要在gridview中获取该特定员工的数据..



gridview应该具有以下内容/>


EmpID，姓名和我需要显示加息金额和加息日期（如1月12日，2月13日）





假设1月11日起薪是6000，薪水在2月12日上涨11000和8月13日为16000 ..



现在我只需要显示所有值中的那些值..



i需要将它带到一个新的数据表并绑定它到gridview ..

怎么做排智迪stinct ..？



帮我继续进行

Hi..

I have a table called Hikes. Well the data into it is inserted through an excel sheet from the .aspx page..

The columns goes as follows..


EmpID Name Jan-11 Feb-11 Mar-11

and so on it continues..

It has data of 3 years..

And now i have .aspx page where i can give empid.. on entering the empid ,based on it i need to fetch the data of that particular employee in a gridview..

The gridview should have the folowing

EmpID,Name and with that i need to show the hike amount and hike date(like Jan-12 , Feb-13)


Suppose if the starting salary is 6000 on Jan-11 and salary is hiked on Feb-12 as 11000 and Aug-13 as 16000 ..

now i need to show only those values from all the values..

i need to carry this to a new datatable and bind it to gridview..
how to do row wise distinct..?

Help me to proceed further

推荐答案

string conStr,sqlQuery;
int sal;
SqlConnection con = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = null;
DataTable dt = new DataTable();
con.Open();
cmd.Connection = con;

//make data for testing  #saltable for salaries and #salhike to store salary hikes
sqlQuery = "create table #saltable(empid varchar(5),empname varchar(10),jan11 int,feb11 int,mar11 int)";
cmd.CommandText = sqlQuery;
cmd.ExecuteNonQuery();
sqlQuery = "create table #salhike(empid varchar(5),hike int,date varchar(10))";
cmd.CommandText = sqlQuery;
cmd.ExecuteNonQuery();
sqlQuery = "insert into #saltable select '12','ram',10,10,20";
cmd.CommandText = sqlQuery;
cmd.ExecuteNonQuery();

sqlQuery = "SELECT empid,empname,jan11,feb11,mar11 from #salTable where empid='"+ txtItem.Text +"'";
da = new SqlDataAdapter(sqlQuery, con);
da.Fill(dt);
sal = Convert.ToInt32(dt.Rows[0][2].ToString());
for (int i = 0; i < dt.Rows.Count; i++)
    for (int j = 2; j < dt.Columns.Count; j++) //from third column to last            {
        //if there is hike in salary
        if (sal < Convert.ToInt32(dt.Rows[i][j].ToString()))
        {
            string hike = Convert.ToString(Convert.ToInt32(dt.Rows[i][j].ToString()) - sal);
            sal = Convert.ToInt32(dt.Rows[i][j].ToString());   //set this as new base value
            //insert difference in a table
            sqlQuery = "INSERT into #salHike(empid,hike,date)";
            sqlQuery += " SELECT '"+ txtItem.Text +"',"+ hike +",'"+ dt.Columns[j].ColumnName +"'";
            cmd.CommandText = sqlQuery;
            cmd.ExecuteNonQuery();
        }
    }
dt = new DataTable();

sqlQuery = "SELECT * from #salHike";
da = new SqlDataAdapter(sqlQuery, con);
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();

输出 -



empid加息日期

12 10月3日

Output -

empid hike date
12 10 mar11

这篇关于根据条件将表中的选定值转换为另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！