none
Help with a query

    Question

  • I have the following table and would like a query that lists the startdate, enddate and anotherdate as columns where the atype is either red or green.

    Aref

    Adescription

    Atype

    startdate

    enddate

    anotherdate

    100

    Ted

    Red

    14/02/2001

    14/3/2003

    28/2/2002

    100

    Ted

    Green

    15/2/2005

    18/2/2005

    19/2/2005

    100

    Ted

    Blue

    17/9/2007

    25/12/2007

    30/12/2008

    200

    Fred

    Green

    1/1/2008

    3/1/2009

    2/2/2008

    200

    Fred

    Blue

    5/4/2005

    8/4/2013

    1/5/2012

    200

    Fred

    Red

    2/4/2007

    4/4/2008

    6/4/2011

    The results should look like this below:

    Aref

    Adescription

    Red_startdate

    Red_enddate

    Red_anotherdate

    Green_startdate

    Green_enddate

    Green_anotherdate

    100

    Ted

    14/2/2001

    14/3/2003

    28/2/2002

    15/2/2005

    18/2/2005

    19/2/2005

    200

    Fred

    2/4/2007

    4/4/2008

    6/4/2011

    1/1/2008

    3/1/2009

    2/2/2008

    Thursday, September 04, 2014 8:04 PM

All replies

  • select  Aref,Adescription,
    Max(case when Atype='Red' Then startdate end) as Red_startdate,
     Max(case when Atype='Red' Then enddate end) as Red_enddate,
      Max(case when Atype='Red' Then anotherdate end) as Red_anotherdate,
      Max(case when Atype='Green' Then startdate end) as Green_startdate,
     Max(case when Atype='Green' Then enddate end) as Green_enddate,
      Max(case when Atype='Green' Then anotherdate end) as Green_anotherdate
     from test8
     group by Aref,Adescription

    Thursday, September 04, 2014 8:19 PM