Transpose into fixed column

[east7]

Hi all,

I am using SQL 2000, and I have following table with 2 columns, ID and TIME

ID | TIME
A | 07:01
A | 12:05
A | 13:01
A | 15:09
B | 15:07
C | 06:52
C | 12:47
C | 17:03
D | 07:00
D | 18:12
D | 18:57
D | 21:04

Now i want to transpose them into another existing table with 7 fixed columns: ID, T1..T6
So it should be like this:

ID | T1 | T2 | T3 | T4 | T5 | T6
A | 07:01 | 12:05 | 13:01 | 15:09
B | - | - | - | 15:07
C | 06:52 | - | 12:47 | 17:03
D | 07:00 | - | - | 18:12 | 18:57 | 21:04

Have tried using following query

SELECT c.ID,
CASE
WHEN c.Time <= '07:15' THEN c.Time
END AS T1,
CASE
WHEN c.Time > '07:15' AND c.Time <= '12:15' THEN c.Time
END AS T2,
CASE
WHEN c.Time > '12:15' AND c.Time <= '13:15' THEN c.Time
END AS T3,
CASE
WHEN c.Time > '13:15' AND c.Time <= '18:15' THEN c.Time
END AS T4,
CASE
WHEN c.Time > '18:15' AND c.Time <= '19:15' THEN c.Time
END AS T5,
CASE
WHEN c.Time > '19:15' AND c.Time <= '21:30' THEN c.Time
END AS T6
FROM Cards c
WHERE c.Date = '03/20/2007'
ORDER BY c.ID, c.Time

The result is not far from ok.

Any help will be appreciated.

TIA
east7

[ck9663]

use PIVOT/UNPIVOT

Good luck

--- CK

[east7]

ck, thanks. Cant find pivot in sql 2000.
However you lead me to this:

SELECT c.ID,

MAX(
CASE
WHEN c.Time <= '07:15' THEN c.Time
END)
AS T1,

MAX(
CASE
WHEN c.Time > '07:15' AND c.Time <= '12:15' THEN c.Time
END)
AS T2,

MAX(
CASE
WHEN c.Time > '12:15' AND c.Times <= '13:15' THEN c.Time
END)
AS T3,

MAX(
CASE
WHEN c.Time > '13:15' AND c.Time <= '18:15' THEN c.Time
END)
AS T4,

MAX(
CASE
WHEN c.Time > '18:15' AND c.Time <= '19:15' THEN c.Time
END)
AS T5,

MAX(
CASE
WHEN c.Time > '19:15' AND c.Time <= '21:30' THEN c.Time
END)
AS T6

FROM Cards c
GROUP BY c.ID
ORDER BY c.ID

Now the result is ok.
Thanks again.