PostgreSQL: How to display comon results from the same table in different columns?

Question

I have a table in Postgres database called FruitMarket and the columns are Fruit, Market, Rate and Vendor.

Fruit	Market	Rate	Vendor
Apple	Sector3	50	alpha
Banana	Sector9	10	beta
Mango	Sector3	60	gamma
Orange	Sector3	40	delta
Banana	Sector3	15	epsilon
Mango	Sector9	70	omega
Orange	Sector9	35	sigma
Apple	Sector9	45	kappa

I want to segregate the Fruit column depending on Market column in such a way that Sector3 is in one column and Sector9 is in a different column in the same table; along with their respective rates and vendors. As shown below:

Fruit	Market-1	Rate-1	Vendor-1	Market-2	Rate-2	Vendor-2
Apple	Sector3	50	alpha	Sector9	45	kappa
Banana	Sector3	15	epsilon	Sector9	10	beta
Mango	Sector3	60	gamma	Sector9	70	omega
Orange	Sector3	40	delta	Sector9	35	sigma

I am guessing it will involve INNER JOIN to achieve the second table, but I cannot understand how to give the condition to segregate the Fruit Column using Market Column and display the second table.

The code I tried was

SELECT rate as rate1, vendor as vendor1
FROM FruitMarket F1
INNER JOIN FruitMarket F2
ON F1.fruit = F2.fruit

Now I am not able to figure out the condition I have to give for Market and how to display all the required columns side by side

Answer 1

See if it helps you :

    SELECT rate as rate1, vendor as vendor1
    FROM FruitMarket F1
    INNER JOIN (SELECT * FROM FruitMarket WHERE Market='Sector3') F2
    ON F1.fruit = F2.fruit
    INNER JOIN (SELECT * FROM FruitMarket WHERE Market='Sector2') F3
    ON F1.fruit = F3.fruit

The join with a subquery can be used to emulate a table join but with custom results