0

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

stuckincode
  • 1

1 Answers1

0

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

mwalter
  • 102
  • 5
  • Thanks for the approach @mwalter, but let's say there are too many values in Market column, then the condition (SELECT * FROM...) becomes redundant and we have to find a way to select Market values and statement condition to accommodate all the values in Market column – stuckincode Dec 13 '22 at 13:05
  • A SQL generator can be used to make it easier such as : SELECT 'SELECT rate as rate1, vendor as vendor1 FROM FruitMarket F1 ' UNION ALL SELECT 'INNER JOIN ( SELECT * FROM FruitMarket WHERE Market='||$$'$$||Market||$$'$$||' ) AS '||market||' ON t1.fruit = '||market||'.fruit ' FROM FruitMarket GROUP BY FruitMarket; – mwalter Dec 13 '22 at 15:10