reformating worksheet

[Warren]

I'm trying to import an excel spreadsheet into sql server.
However the format in excel is impossible to work with.
There are hundreds of students and each student may or may
not have 20 classes all horizontaly on the spreadsheet.
I need to break them out verticaly like a table.


ie.

col1 col2 col3 col4 col5 col6 col7 col8 col9
1234 class1 A class2 B class3 C class4 D
5678 class1 C class8 C class9 D class3 B

can this be converted into this on a seperate worksheet?

col1 col2 col3
1234 Class1 A
1234 class2 B
1234 class3 c
1234 class4 D
5678 class1 C
5678 class8 C
5678 class9 D
5678 class3 B

This would save me an incredible amount of time.
Any help/advice would be greatly appreciated.

thanks in advance,
warren

 

[onedaywhen]

It not impossible to work with if you use the UNION keyword:

SELECT
Col1, Col2, Col3
FROM [Sheet1$]
UNION
SELECT
Col1, Col4 AS Col2, Col5 AS Col3
FROM [Sheet1$]
UNION
SELECT
Col1, Col6 AS Col2, Col7 AS Col3
FROM [Sheet1$]
UNION
SELECT
Col1, Col8 AS Col2, Col9 AS Col3
FROM [Sheet1$]

If you have to do this many times with different data in the same
format, it's better if you can work with the format rather than have
to change it each time.