How do I turn Rows to fixed Columns in SQL query

Question

Hi help me with the below query: My current data table are as follow.

flight_id	route
1	BAHRAIN
2	VIENNA
2	DUBAI
3	DUBAI
3	COCHIN
3	DOHA
3	COLOMBO
4	LONDON
4	MOSCOW
4	CHENNAI
4	DELHI
4	VNUKOVO
4	DUBAI

I want transpose my data table into below format. I think it is something like Pivot, but can't figure out how to do it. :)

flight_id	route1	route2	route3	route4	route5	route6
1	BAHRAIN	N/A	N/A	N/A	N/A	N/A
2	VIENNA	DUBAI	N/A	N/A	N/A	N/A
3	DUBAI	COCHIN	DOHA	COLOMBO	N/A	N/A
4	LONDON	MOSCOW	CHENNAI	DELHI	VNUKOVO	DUBAI

Please help me!

Thank!

What's your logic to determin which city belongs to route N? — Stu, Dec 05 '21 at 10:56
N/A in some routes means that there is no data for that route, for an example flight_id 1 there is only one route and it is to select as route1 and flight_id 4 there are 6 routes in that id which will select in entire 6 routes. I want select upto 6 routes. — Aly, Dec 05 '21 at 11:20
That doesn't address the question - eg why is DELHI `route4` and not any other? Note - "because that's how I have listed it on screen" is not applicable. — Stu, Dec 05 '21 at 11:23
Rows in a table have no defined order. Without any logic based on columns within the table, it is IMPOSSIBLE to know that flight 4 "starts" with London and continues on to Moscow then Chennai etc. in that specific order. Maybe that is not important and you don't care how "routes" are ordered for any flight. That idea is generally very rare. — SMor, Dec 05 '21 at 11:59

score 1 · Answer 1 · answered Dec 05 '21 at 11:31

The solution for this is typically to use an aggregated conditional case expression.

I have ordered the cities alphabetically to determine the route 1-6; if you have a different requirement, amend the order by criteria of the row_number function to use the relevant column or expression.

with r as (
    select *, Row_Number() over(partition by flight_id order by route) rn
    from flights
)
select 
    flight_id,
        Max(case when rn = 1 then route end) Route1,
        Max(case when rn = 2 then route end) Route2,
        Max(case when rn = 3 then route end) Route3,
        Max(case when rn = 4 then route end) Route4,
        Max(case when rn = 5 then route end) Route5,
        Max(case when rn = 6 then route end) Route6
from r
group by flight_Id

Pato · Answer 2 · 2021-12-05T23:05:35.353

Here you have a pivot alternative.

There's no need to worry about the order by (select null) clause read about this here but you have to set some field in order to keep the sequence.

select * from
(
  select
    [flight_id], 
    [route],
    concat('route',
      (row_number() over (partition by [flight_id] order by (select null)))
    ) [routeId]
  from [routes]
) t  
pivot  
(  
  max([route]) 
  for [routeId] in ([route1], [route2], [route3], [route4], [route5], [route6])  
) p 


-- result
/**
 * +-----------+---------+--------+---------+---------+---------+--------+
 * | flight_id | route1  | route2 | route3  | route4  | route5  | route6 |
 * +-----------+---------+--------+---------+---------+---------+--------+
 * |         1 | BAHRAIN | NULL   | NULL    | NULL    | NULL    | NULL   |
 * |         2 | VIENNA  | DUBAI  | NULL    | NULL    | NULL    | NULL   |
 * |         3 | DUBAI   | COCHIN | DOHA    | COLOMBO | NULL    | NULL   |
 * |         4 | LONDON  | MOSCOW | CHENNAI | DELHI   | VNUKOVO | DUBAI  |
 * +-----------+---------+--------+---------+---------+---------+--------+
 */

How do I turn Rows to fixed Columns in SQL query

2 Answers2