transform a resultset

[plork123]

hi all

i'm doing a simple select statement and the results are being displyaed like this

firstname lastname id type phone
-------------------------------------------------------
Joe Blogs 1 Work 0123456789
Joe Blogs 1 Home 9876543210

I want the results deisplayed in one line for each person, like this


firtname lastname id typework typehome
-----------------------------------------------------------------
Joe Blogs 1 0123456789 9876543210

Can someone show me how i would do this, would i need to write a cursor to transform the data'?

thanks for any help

 

[feherke]

Hi

This one need to have both records for each person.

[b]select[/b] firstname,lastname,id,w.phone [b]as[/b] typework,h.phone [b]as[/b] typehome [b]from[/b] plork123 w [b]join[/b] plork123 h [b]using[/b] (firstname,lastname,id) [b]where[/b] w.type=[i]'Work'[/i] [b]and[/b] h.type=[i]'Home'[/i];

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

Bigger is better. This one works if some persons have only one record.

[b]select[/b] firstname,lastname,id,w.phone [b]as[/b] typework,h.phone [b]as[/b] typehome [b]from[/b] ([b]select[/b] * [b]from[/b] plork123 [b]where[/b] type=[i]'Work'[/i]) w [b]full join[/b] ([b]select[/b] * [b]from[/b] plork123 [b]where[/b] type=[i]'Home'[/i]) h [b]using[/b] (firstname,lastname,id);

Feherke.

http://rootshell.be/~feherke/