Insert rows based on column values

[scabral79]

Hi,

i have the following table:

Name ID1 ID2 ID3 ID4
Scott 1111 2222 3333 4444
Jim 5555
Joe 6666 7777

I need to create a new table based on these values to look like this:

Name ID
Scott 1111
Scott 2222
Scott 3333
Scott 4444
Jim 5555
Joe 6666
Joe 7777

is there a way to do this in SQL?

thanks

[chazzy]

you need to use UNION.

[scabral79]

example?

[chazzy]

assuming the table's already created..


insert into this_new_table(Name,ID)
(
select Name,ID1 as ID from current_table where ID1 NOT NULL
union
select Name,ID2 as ID from current_table where ID2 NOT NULL
union
...
)