CODEWITHSUNDEEP
sqloracle-database
user1793001
user1793001

Reputation: 23

How to merge rows with oracle sql

Im having trouble declaring an object from a row so it could be use in another column

This are the value inserted into a table TRIPLEG with format TripNum#, Length#, Origin, Destination

INSERT INTO TRIPLEG VALUES(10, 1, 'Perth', 'Sydney');
INSERT INTO TRIPLEG VALUES(10, 2, 'Sydney', 'Brisbane');
INSERT INTO TRIPLEG VALUES(11, 1, 'Sydney', 'Melbourne');

How do i actually merge them in a way so it will look like 

T#   ORIGIN     DESTINATION1        DESTINATION2
---- ---------- ------------------- ----------------
10   Perth      Sydney              Brisbane
11   Sydney     Melbourne

I was asked to create a relational view, I was guessing it would look something like this:

Create view 
as select t#, origin, destination, destination
from TRIPLEG

But the Destination2 is the part where im not sure what to put. Could anyone enlighten me on this?

Upvotes: 0

Views: 1646

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19245

How many legs of the trip can there be? 3? 100? What you're looking for is a crosstab.

You haven't indicated what the columns in TRIPLEG are, so I'll assume t#, leg, origin, destination.

For the very specific case you have described, this will work:

SELECT 
"t#", 
MAX(DECODE(leg,1,origin)) origin, 
MAX(DECODE(leg,1,destination)) destination,
MAX(DECODE(leg,2,destination)) destination2
FROM TRIPLEG
GROUP BY "t#"

For just about any other case there are problems with it.

-If there are more than two legs

-If leg one finishes in Sydney but leg 2 doesn't start in Sydney

etc.

Try this, investigate crosstabs and ask more questions.

Upvotes: 2

Related Questions