In article <[EMAIL PROTECTED]>,
"Sam Russo" <[EMAIL PROTECTED]> writes:
> I recieve a delimited file whose fields are:
> day,slot,subject,room
> An example of this file is:
> 2,1,Mat,R1
> 3,1,Sci,R6
> 1,2,Sci,R6
> 3,2,Mat,R3
> 1,3,Eng,R2
> 2,3,Eng,R5
> 1,4,Mat,R7
> 3,4,Eng,R9
> I need a mysql query that will generate a timetable which looks like:
> Day1 Day2 Day3
> -------------------------------
> 1 Mat R1 Sci R6
> 2 Sci R6 Mat R3
> 3 Eng R2 Eng R5
> 4 Mat R7 Eng R9
If there are only three days, you could use the following:
CREATE TEMPORARY TABLE slots (slot TINYINT UNSIGNED NOT NULL PRIMARY KEY) AS
SELECT DISTINCT slot FROM tbl;
SELECT s.slot,
coalesce(concat(t1.subject, ' ', t1.room), '') AS Day1,
coalesce(concat(t2.subject, ' ', t2.room), '') AS Day2,
coalesce(concat(t3.subject, ' ', t3.room), '') AS Day3
FROM slots s
LEFT JOIN tbl t1 ON t1.slot = s.slot AND t1.day = 1
LEFT JOIN tbl t2 ON t2.slot = s.slot AND t2.day = 2
LEFT JOIN tbl t3 ON t3.slot = s.slot AND t3.day = 3;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
