Hi John, I didn't know you can do that! (such a novice indeed!) Thank you for your reply, I will put it to the test first thing when i get back to the office tomo.
Cheers "John Hicks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > tony yau wrote: > > Hello, > > > > I can get a select result like the following: (SELECT ID,Changes FROM > > mytable WHERE somecondition;) > > > > ID Changes > > ----------------- > > 1 10.0 > > 1 10.3 > > 1 12.2 > > 2 31.0 > > 3 1.02 > > 3 4.9 > > > > how can I get the above result sets into the following format (columns > > 'Changes1','Changes2',... are all from 'Changes') > > > > ID Changes1 Changes2 Changes3 (limits of 5) > > -------------------------------------------- > > 1 10.0 10.3 12.2 > > 2 31.0 > > 3 1.02 4.9 > > > > > > I have got a method that works (I think) by first do a SELECT getting > > DISTINCT id values and then foreach of these ID I do another SELECT to get > > the Changes values and then just massage the display. > > > > Is there another way of doing this by using a single SQL query? > > There may be a simpler way, but this should work: > > select Table.ID, > Table1.Changes as Changes1, > Table2.Changes as Changes2, > Table3.Changes as Changes3 > > from Table, > Table as Table1, > Table as Table2, > Table as Table3 > > where Table.ID = Table1.ID > and Table.ID = Table2.ID > and Table.ID = Table3.ID > > and Table1.[condition for Changes1] > and Table2.[condition for Changes2] > and Table3.[condition for Changes3] > > order by table.ID > > > --J > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]