how to move data

Question

I have a table with data as following:

id  col1  col2
1    c1
1         c2
2    b1
3         a3
4    e1
4         e2

Now I need to move this data in another table in such a way that the record that has same id goes as a single record in this new table.

so the new table data would look like this:

id     col1    col2
 1       c1      c2
 2       b1
 3               a3
 4       e1      e2

now how do we do that?

Answer 1

select id, max(col1) col1, max(col2) col2
into NewTable
from [Table] t 
group by id

Answer 2

i don't have a setup to test with, but something like the following should work:

select a.id,a.col1,b.col2
into fixed_table
from table1 a
inner join table1 b on a.id = b.id
where a.col1 is not null
and b.col2 is not null;

That should create fixed_table from table1. If you want to move the data back into table1, you could do:

drop table1;
select id, col1, col2
from fixed_table
into table1;