dbdb please come in f642004-08-26 05:31:43

I have a table like talbe1 below that I want to convert to table2.

table1
name math physics chemistry biology ....
john 95 90 100 85 ....

table2
name course grade
john math 95
john physics 90
john chemistry 100
.
.
.


I know the following will work but there are maybe 100 columns in talbe1 so it will be very tedious and am not very confortable having the strings hard coded in it. do you see any better way to accomplish it? thanks.

insert table2 (select name, 'math', math from table1 where name='john');
insert table2 (select name, 'physics', physics from table1 where name='john');
insert table2 (select name, 'chemistry', chemistry from table1 where name='john');
.
.
.

u need a script that creates