how to merge two rows with only one unique column

[gijokjoseph]

I have a problem,The table like this

emp_id emp_name dept
------------ ----------------- ---------

First Row-> 1 Sachin Phy

Second Row-> 1 Sachin Maths

Third Row-> 2 Dravid Che


After executing a query I need the table like this
emp_id emp_name dept1 dept2
------------ ----------------- --------- --------

First Row->1 Sachin Phy Maths

Second Row-> 2 Dravid Che

Please help me...............Thank you in advance

[azimmer]

I have a problem,The table like this

emp_id emp_name dept
------------ ----------------- ---------

First Row-> 1 Sachin Phy

Second Row-> 1 Sachin Maths

Third Row-> 2 Dravid Che


After executing a query I need the table like this
emp_id emp_name dept1 dept2
------------ ----------------- --------- --------

First Row->1 Sachin Phy Maths

Second Row-> 2 Dravid Che

Please help me...............Thank you in advance

If you have only two depts an emp may belong to, the following code helps. If you have an arbitrary number of such depts a lot more complex process is needed (and will still not get proper columns but a string). If you need real columns in the latter case an even more complex solution is needed. Please let us know what you do need.

select t1.emp_id, t1.emp_name, t1.dept, t2.dept
from mytable t1 inner join mytable t2 on t1.emp_id=t2.emp_id
where (t1.dept<t2.dept)
union 
select t1.emp_id, t1.emp_name, t1.dept, NULL
from mytable t3
where t3.emp_id not in (select t1.emp_id from mytable t1 inner join mytable t2 on t1.emp_id=t2.emp_id
where (t1.dept<t2.dept))
 

[fish fish]

you can create the temp table that the fields are 'emp_id,emp_name,dept1,dept2'

checking the value in dept1, if there has value, put it to dept2.

I have a problem,The table like this

emp_id emp_name dept
------------ ----------------- ---------

First Row-> 1 Sachin Phy

Second Row-> 1 Sachin Maths

Third Row-> 2 Dravid Che


After executing a query I need the table like this
emp_id emp_name dept1 dept2
------------ ----------------- --------- --------

First Row->1 Sachin Phy Maths

Second Row-> 2 Dravid Che

Please help me...............Thank you in advance