turn rows data into columns data

[misc00500]

this is probably a stupid question to ask but, I was wondering if there was a way to turn attach rows of result set from a subselect into column data of the outer select.  IE.

 

turn this:

 

prop_id |      extra_photo      |      caption

-----------------------------------------------

123      |      1.jpg                |  123 spring

123      |      2.jpg                |  123 fairfax

124      |      3.jpg                |  123 freelance

 

 

into this:

 

 

property_id  |  address  |  city |    caption_1    |  caption_2    | caption_3

---------------------------------------------------------------------------

123            |    blah    |  blah  |  123 spring    |    123 fairfax  |  NULL

124            |    blah    | blah  |  123 freelance |    NULL        |    NULL

 

 

 

 

[bubblegum.anarchy]

Columns can not be created dynamically.  A group_concat can group all the captions into one column and add a seperator as well... have a look at the group_concat information here: http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

[misc00500]

thanks alot bubblegum.  boy, for someone who's name is bubblegum, you sure do know alot.   But I was hoping to avoid using group_concat, as I'll have to use php string functions to extract each individual captions out. 

[bubblegum.anarchy]

Only one php function would be required.. with using a newline separator in the group_concat...

 

$caption = explode("\n", $record['caption']);

[misc00500]

hm.  that's true.  damn.. I might have to rewrite my table structure...  thanks alot bubble

[fenway]

Columns can not be created dynamically.  A group_concat can group all the captions into one column and add a seperator as well... have a look at the group_concat information here: http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

Yeah, but in this case, you could "JOIN" these two selects based on the ID column.