convert columns to Rows

[Neo456]

I wonder if anyone can help?
I have a table call T1 that generates the following results
(not complete results)

Date Lost School
2003-10-20, 4, 4700
2003-10-21, 13, 4700
2003-10-23, 5, 4700
2003-11-05, 12, 4700
2003-10-20, 8, 4042
2003-10-21, 4, 4042
2003-10-23, 1, 4042
2003-11-05, 0, 4042

What I would like to do is generate a view on the fly from this to convert the columns to rows so it will read like this putting all schools in one row each?

20/10/03 21/10/03 23/10/03 05/11/03
4700 4 13 5 12
4042 8 4 1 0

Many thanks
Neo456

 

[mimi2]

have a look at this

thread183-690231

 

[sneki]

--set dateformat dmy
select distinct school,
(select lost from tableName t2 where t2.school = t1.school and t2.[date] = '20/10/2003') as [20/10/03],
(select lost from tableName t2 where t2.school = t1.school and t2.[date] = '21/10/2003') as [21/10/03],
(select lost from tableName t2 where t2.school = t1.school and t2.[date] = '23/10/2003') as [23/10/03],
(select lost from tableName t2 where t2.school = t1.school and t2.[date] = '05/11/2003') as [05/11/03]
from tableName t1
order by school