From: Harald Fuchs
Date: June 18 2004 2:46pm
Subject: Re: tricky timetable based query
List-Archive: http://lists.mysql.com/mysql/167513
Message-Id: <pu659o6i03.fsf@srv.protecting.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII

In article <40D36E0F.1213.15F859F@localhost>,
"Sam Russo" <sam@stripped> 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;