469,330 Members | 1,289 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

how to merge two rows with only one unique column

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
Aug 14 '07 #1
2 9070
azimmer
200 Expert 100+
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.

Expand|Select|Wrap|Line Numbers
  1. select t1.emp_id, t1.emp_name, t1.dept, t2.dept
  2. from mytable t1 inner join mytable t2 on t1.emp_id=t2.emp_id
  3. where (t1.dept<t2.dept)
  4. union 
  5. select t1.emp_id, t1.emp_name, t1.dept, NULL
  6. from mytable t3
  7. where t3.emp_id not in (select t1.emp_id from mytable t1 inner join mytable t2 on t1.emp_id=t2.emp_id
  8. where (t1.dept<t2.dept))
  9.  
Aug 15 '07 #2
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
Aug 16 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Simon Jackson | last post: by
5 posts views Thread by Bernd Hohmann | last post: by
2 posts views Thread by Bob | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.