How do I turn Rows to fixed Columns in SQL query-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

Here you have a pivot alternative.

There's no need to worry about the order by (select null) clause read about this here but your have to set some field in order to preserve 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  |
 *  ----------- --------- -------- --------- --------- --------- -------- 
 */