Move to 1 row

• girl_bj

  SSCommitted

  Points: 1731

  More actions

  October 7, 2014 at 8:48 pm

  #397473

  I have data something like this:

  ID Date Route routeDate TripType

  A1 20141001 UK-US 20141229 Return

  A1 20141001 US-UK 20141231 Return

  A2 20141001 AUS-UK 20141018 OneWay

  I would like to move the return triptype ID to 1 row instead of duplicate in 2 rows.

  If is one way will leave it as NA.

  How can I do that?

  ID Date Route1 routeDate1 Route2 routeDate2 TripType

  A1 20141001 UK-US 20141229 US-UK 20141001 Return

  A2 20141001 AUS-UK 20141018 NA NA OneWay

• Ville-Pekka Vahteala

  SSCrazy

  Points: 2458

  More actions

  October 8, 2014 at 12:21 am

  #1750828

  But why would You want to do that? Just outer join to data for select queries.

• girl_bj

  SSCommitted

  Points: 1731

  More actions

  October 8, 2014 at 12:30 am

  #1750829

  would like to see the ID in 1 row instead of duplication.

• Ville-Pekka Vahteala

  SSCrazy

  Points: 2458

  More actions

  October 8, 2014 at 12:57 am

  #1750831

  Here is example how to do it with select.

  WITH cte AS

  (

  SELECT 'A1' AS id, 20141001 AS [date], 'UK-US' AS route, 20141229 AS routeDate, 'Return' AS TripType UNION ALL

  SELECT 'A1', 20141001, 'US-UK', 20141231, 'Return' UNION ALL

  SELECT 'A2', 20141001, 'AUS-UK', 20141018, 'OneWay'

  )

  , final AS

  (

  SELECT ROW_NUMBER() OVER (PARTITION BY cte.id,cte.[date] ORDER BY cte.id,cte.[date])AS row

  ,cte.id

  ,cte.[date]

  ,cte.route

  ,cte.routeDate

  ,outercte.route AS route2

  ,outercte.routeDate AS routeDate2

  , cte.TripType

  FROM cte

  LEFT JOIN cte AS outercte ON cte.id = outercte.id

  AND cte.route <> outercte.route

  )

  SELECT final.id

  ,final.[date]

  ,final.route

  ,final.routeDate

  ,final.route2

  ,final.routeDate2

  ,final.TripType

  FROM final

  WHERE row = 1