Transposing rows

[rafeman]

This may or not be possible, but here we go;

I have a SQL table as such;

SchoolNo Notes Date
1000 Visited 01/09/06
1021 Not interested 10/06/06
1021 Now interested 11/06/06
1000 Spoke to teacher 10/07/06
1000 not interested 10/09/06

Is there a way we can convert the row to columns so each row is a schoolno with seperate columns for each row. Ideally it would need to be column1 - latest date to last column oldest date.

E.g.
SchoolNoNote1 Date1 Note2 Date2
1000 not interested 10/09/06 visited 01/09/06 and so on
1021 now interested 11/06/06 not interested 10/06/06

Is it possible?

Thanks

 

[GeertVerhoeven]

Hi,

If I get it right, you want to have your columns be expandable. I wouldn't go for such a solution since it is bad for performance and bad for maintainability.

Also not that there is a limit in the number of columns.

A better aproach would be to create multiple tables like this schema:

SCHOOL
ID int
NAME varchar

NOTES
ID int
SCHOOL_ID int
NOTE varchar

Eventually if the notes are always from a list, you can set them also in a dedicated table.

Greetz,

Geert



Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[GeertVerhoeven]

Sorry, forgot the DATE field. The NOTES table should be like this:

NOTES
ID int
SCHOOL_ID int
DATE datetime
NOTE varchar



Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog