The following SELECT should produce your table
mysql> Select id,
-> Max(CASE TimeSlot
-> WHEN 1 THEN Subject
-> ELSE ""
-> END) AS TS1,
-> Max(CASE TimeSlot
-> WHEN 2 THEN Subject
-> ELSE ""
-> END) AS TS2,
-> Max(CASE TimeSlot
-> WHEN 3 THEN Subject
-> ELSE ""
-> END) AS TS3,
-> Max(CASE TimeSlot
-> WHEN 4 THEN Subject
-> ELSE ""
-> END) AS TS4
-> From sch
-> Group by id
-> ;
+-----+----------+--------+------+--------+
| id | TS1 | TS2 | TS3 | TS4 |
+-----+----------+--------+------+--------+
| 215 | Science1 | Maths1 | Eng1 | |
| 648 | | Maths2 | | Art |
| 901 | Science2 | | Eng1 | French |
+-----+----------+--------+------+--------+
3 rows in set (0.00 sec)
-----Original Message-----
From: Sam Russo [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 5:25 PM
To: [EMAIL PROTECTED]
Subject: A difficutl query I cannot do.
I have a delimited file sent to me with students ID subjects and when
they
do these subjects. This file (table) looks like:
ID TimeSlot Subject
++++++++++++++++++++++++++++++++++++
215 3 Eng1
648 2 Maths2
901 4 French
215 2 Maths1
901 1 Science2
648 4 Art
215 1 Science1
901 3 Eng1
I need to produce the following output (on a web page using php and
mysql) with a mysql query.
ID 1 2 3 4
215 Science1 Maths1 Eng1
648 Maths2 Art
901 Science2 Eng1
French
any help would be appreciated.
sam russo
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
