Query to combine several "records/rows" into one "record/row"?

Im new to SQL, Ive been getting by building lists from our student database by building or teacher (etc.). These have been all records that exist on only one row, so its been easy (so far).
Heres what Im trying to do and I dont even know what to call it, so I'm not even sure what to search for...

Ive got a MS SQL 6.5 database with the following:
ACTIVE students: each student has ID_NUM[8 digits], NAME, GRADE, SCHOOL with one rowof 4 data items per student.

SCHEDULE of courses with (student) ID_NUM[8 digits], SEMESTER[S1 or S2], HOUR[1-7], COURSE_NAME, ROOM_NUM with 14 records (rows) with these 5 items, in this SCHEDULE database for each student.

My mission is to combine then in to one row using the student ID_NUM as the key. (This is to help me with several things, spreadsheets/database for others to easily use, export to simple databases for teacher handhelds.).

Id like one row of the 75 items combined, resulting in 32 items (ACTIVE 4 items + 14 * 2 SCHEDULE items [COURSE_NAME, ROOM_NUM]) since I want stuff plugged into the correct field, for each student. I'd refer to this as COMBINEDRECORD and Id turn the field names into the following:
ID_NUM[8 digits], NAME, GRADE, SCHOOL, S1HOUR1_NAME, S1HOUR1_ROOM_NUM, S1HOUR2_NAME, S1HOUR2_ROOM_NUM, S1HOUR3_NAME, S1HOUR3_ROOM_NUM, S1HOUR4_NAME, S1HOUR4_ROOM_NUM, S1HOUR5_NAME, S1HOUR5_ROOM_NUM, S1HOUR6_NAME, S1HOUR6_ROOM_NUM, S1HOUR7_NAME, S1HOUR7_ROOM_NUM, S2HOUR1_NAME, S2HOUR1_ROOM_NUM, S2HOUR2_NAME, S2HOUR2_ROOM_NUM, S2HOUR3_NAME, S2HOUR3_ROOM_NUM, S2HOUR4_NAME, S2HOUR4_ROOM_NUM, S2HOUR5_NAME, S2HOUR5_ROOM_NUM, S2HOUR6_NAME, S2HOUR6_ROOM_NUM, S2HOUR7_NAME, S2HOUR7_ROOM_NUM

I dont care if there are any blanks I just want to get the data if
SEMESTER='S2', HOUR='5' & COURSE_NAME='Basketweaving' & ROOM_NUM='Pool'
to end up being in the right spot (S2 and Hour 5) in the new COMBINEDRECORD row with
S2HOUR5_NAME='Basketweaving' & S2HOUR5_ROOM_NUM='Pool' for the correct student ID_NUM. Of course with the correct ACTIVE student info into the same "row"

Does that make sense? It might not be the best way, but itll make the data more accessible to everyone and some programs we already use with our old student system. Obviously theres more data than that but I think this is enough to explain my issue and give me enough to work with

Any help, directions to a webpage or book with the correct terms to look up would be very helpful.

Thank you for any help or direction you can give,
GarySounds like a join. Are you trying to creat a new table or just do a report?|||Or a view with a join if you want to leave the existing tables alone.|||Originally posted by barneyrubble318
Or a view with a join if you want to leave the existing tables alone.

I'll probably be doing two (similar) things:

1) An SQL query that just puts my COMBINEDRECORD table into an Excel Spreadsheet. (Why Excel? Everyone here knows how to merge from it, so they can then manipulate it how they want.)

2) An SQL query from Desktop2MobileDB which will convert the COMBINEDRECORD table into a Palm OS (MobileDB) database so principals and teachers can have more data on hand. (They can see where the kid in the hall is really supposed to be...)

I pretty much do the above two things with data now, the problem is the multi-line data from the SCHEDULE/

If I have to create a new table and then access it from there, I guess I can do that. I might not be able to automate it as easily though...

Thanks,
Gary