Unanswered: Query to combine several "records/rows" into one "record/row"?
I’m new to SQL, I’ve 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 it’s been easy (so far).
Here’s what I’m trying to do and I don’t even know what to call it, so I'm not even sure what to search for...
I’ve got a MS SQL 6.5 database with the following: ACTIVE students: each student has ID_NUM[8 digits], NAME, GRADE, SCHOOL with one “row”of 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.).
I’d 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 I’d 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 don’t 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 it’ll make the data more accessible to everyone and some programs we already use with our old student system. Obviously there’s 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,
Re: Query to combine several "records/rows" into one "record/row"?
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...