SQL Create a new calculated column based on values of multi rows and cols-CodePudding

This is my 1st post so I cannot embed pictures.

I have a data about airline's booking, using Oracle db, sample is structured as below:

Data sample

Recordlocator is booking code
Sequencenmbr: whenever there is a change in booking, it records new status of a booking with higher Sequencenmbr. So the highest Sequencenmbr in the database shows the latest/current status of bookings
Sequenceair: is the sequence of flights in bookings, it may be one or many flights in a booking
DepartAirport: is from airport
ArrAirport: is to airport.

So the question is, I would like to create new Itinerary column that shows full Itinerary of booking in every rows, which is combination of DepartAirport of each row (in order of SequenceAir) and ArrAirport of a last row. Could anyone help me with the SQL statement or give some links to read?

It has to group by Recordlocator, Sequencenmbr and order by SequenceAir. It should look like this:

result

Recordlocator	Sequencenmbr	SequenceAir	DepartureDateTime	DepartAirport	ArrAirport	Itinerary
GQWYGM	32	1	25/11/18 16:40	RGN	SIN	RGN-SIN-JKT-SIN-RGN
GQWYGM	32	2	26/11/18 09:35	SIN	JKT	RGN-SIN-JKT-SIN-RGN
GQWYGM	32	3	29/11/18 06:50	JKT	SIN	RGN-SIN-JKT-SIN-RGN
GQWYGM	32	4	29/11/18 11:00	SIN	RGN	RGN-SIN-JKT-SIN-RGN
GQWYGM	33	1	25/11/18 16:40	RGN	SIN	RGN-SIN-MNL-SIN-RGN
GQWYGM	33	2	26/11/18 09:35	SIN	MNL	RGN-SIN-MNL-SIN-RGN
GQWYGM	33	3	29/11/18 06:50	MNL	SIN	RGN-SIN-MNL-SIN-RGN
GQWYGM	33	4	29/11/18 11:00	SIN	RGN	RGN-SIN-MNL-SIN-RGN

Manythanks

CodePudding user response：

You should use LISTAGG to do this

LISTAGG(DepartAirport||'='||ArrAirport,'-') WITHIN GROUP(ORDER BY SequenceAir)

in your select statement.

CodePudding user response：

Try use CONCAT function for solving it.

alter table t add Itinery nvarchar;

update t
    set Itinery = CONCAT(DepartAirport, ArrAirport)

The query was not tested, and you need to change the table name.