select query transformation 1

[Andyfives]

Hi there folks,

I have a small problem that I can not seem to fix and just need some guidence to get started.

Simplified problem

My source table:-

Table1
PNRS SEGID PAXID FLIGHTDATE
ABC 1 1 10/12/2004
DEF 1 1 11/12/2004
DEF 1 2 11/12/2004
DEF 2 1 19/12/2004
DEF 2 2 19/12/2004
GHI 1 1 05/01/2005
GHI 2 1 07/01/2005

needs a select query that transformd it to:-

PNRS DEPARTURE_DATE RETURN_DATE
ABC 10/12/2004
DEF 11/12/2004 19/12/2004
GHI 05/01/2005 07/05/2005

My usual thing to do is just create some VB code and change as I like there, but I really need to get out of doing this everytime. Can anyone help me?

What command do I need to look up?

BTW MySQL Maestro really is good. I'm beginning to find my way around. I recommend it.

http://www.sqlmaestro.com/index.php

Thank you in advance
Andy

 

[r937]

select PNRS 
     , min(FLIGHTDATE) as DEPARTURE_DATE 
     , max(FLIGHTDATE) as RETURN_DATE
  from Table1
group
    by PNRS

rudy

http://r937.com/

SQL Consulting

 

[Andyfives]

Just found the group_concat funtion too but this could work better.

Thanks for your response. I'll give it a try