Add new rows with identical value except for one column

Question

I am still pretty new to SQL and I don't know whether it is possible. So I have this flight trip table and I need to count how many flights per route. The problem is there is RoundTrip flight that makes it harder to count the way back flight. The table is like this:

source_id	destination_id	date	trip_type
AUH	MAN	2017-01-01	RoundTrip
LHR	CDG	2017-05-12	OneWay
DXB	BHX	2017-12-20	Roundtrip

All I want is turn the table into:

source_id	destination_id	date	trip_type
AUH	MAN	2017-01-01	RoundTrip
MAN	AUH	2017-01-01	RoundTrip
LHR	CDG	2017-05-12	OneWay
DXB	BHX	2017-12-20	Roundtrip
BHX	DXB	2017-12-20	Roundtrip

Therefore I can create a new column later that combine source_id and destination_id, then groupby it to count the total trip per route.

Thank you before for your kind effort to answer this !

Answer 1

You can use insert:

insert into t (source_id, destination_id, date, trip_type)
    select destination_id, source_id, date, trip_type
    from t
    where trip_type = 'Roundtrip';

Note: If there is the possibility of the rows already in the table and you want to avoid duplicates, you can add a not exists clause:

insert into t (source_id, destination_id, date, trip_type)
    select destination_id, source_id, date, trip_type
    from t
    where trip_type = 'Roundtrip' and
          not exists (select 1
                      from t t2
                      where t2.trip_type = t.trip_type and
                            t2.date = t.date and
                            t2.destination_id = t.source_id and
                            t2.source_id = t.destination_id
                     );