SQL "transform" query

Question

I have these data on a table (using SQL Server 2005):

ID    ParentID    StartTime    EndTime
77    62          08:00:00     11:00:00
78    62          12:00:00     15:00:00
79    62          18:00:00     22:00:00

and I want to transform it into this:

ParentID  BreakfastStart  BreakfastEnd  LunchStart  LunchEnd  DinnerStart  DinnerEnd
62        08:00:00        11:00:00      12:00:00    15:00:00  18:00:00     22:00:00

Now the hard part is: assume I have no other data field specifying which record is breakfast, lunch or dinner. I want to associate them with lowest start time, i.e., the lower start time will be breakfast, next lower will be lunch and the higher will be dinner (assume all three (and only three) records are always filled).

Any ideas?

Answer 1

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY parentID ORDER BY StartTime) AS rn
        FROM    mytable
        )
SELECT  qb.ParentID,
        qb.StartTime AS BreakfastStart, qb.EndTime AS BreakfastEnd,
        ql.StartTime AS LunchStart, ql.EndTime AS LunchEnd,
        qd.StartTime AS DinnerStart, qd.EndTime AS DinnerEnd
FROM    q qb
LEFT JOIN
        q ql
ON      ql.parentID = qb.parentID
        AND ql.rn = 2
LEFT JOIN
        q qd
ON      qd.parentID = qb.parentID
        AND qd.rn = 3
WHERE   qb.rn = 1