How to get multiple rows data from a subquery into multiple columns

I have a sub-select in my code which returns a discount a product has. The product might in some instance have multiple discounts (discount on discount for a special promo). When I run the code, I get single row sub-query returns more than one row error. I want it to return all 3 rows but in different columns as discount 1, discount 2 and discount 3.

My code is as follows:

select prod.prod_id, 
       prod.prod_name, 
       st.store, 
       reg.region, 
       (select dis.discount from discounts dis
       where prod.prod_id = dis.prod_id
       and   st.store_cd  = dis.store_id
       and   dis.reg_cd   = reg.reg_cd
       and   dis.eff_dt   <= :dt
       and   (dis.xpir_dt is null or dis.xpir_dt > :dt)
       and  rownum = 1) as discount
from   products prod,
       stores   st,
       region   reg
where  prod.prod_id  = st.prod_id
and    st.reg_cd     = reg.reg_cd

so I want to get rid of the rownum = 1 as it forces only one discount to be returned and return all the 3 discounts in separate columns.

Edit: there are other sub-queries connected to this (it is a longer code and I only put a segment of it). So removing the subquery and then putting it in the main join clause does not work well when joining to the other subqueries.

Edit 2: Sample data:

products table
| prod_id | prod_name|
| ------- | ---------|
| 1       | mangoes  |
| 2       | apples   |

discounts table
| prod_id | discount |
| ------- | ---------|
| 1       | 10%      |
| 1       | 5%       |
| 2       | 3%       |
| 2       | 8%       |
| 2       | 2%       |

There is store and regions table which all have single row entries similar to products table.

The ideal output should be

| prod_id | prod_name| store | region | Discount 1| Discount 2| Discount 3 |
| ------- | ---------| ----- | ------ | --------- | ----------| -----------| 
| 1       | mangoes  | Mega  | GP     | 10%       | 5%        | 0%         |
| 2       | apples   | Mini  | GP     | 3%        | 8%        | 2%         |

CodePudding user response：

Just join the table:

SELECT prod.prod_id, 
       prod.prod_name, 
       st.store, 
       reg.region, 
       dis.discount
 FROM  products prod,
       stores   st,
       region   reg,
       discounts dis      
 WHERE prod.prod_id = st.prod_id
   AND st.reg_cd    = reg.reg_cd
   AND dis.prod_id  = prod.prod_id 
   AND dis.store_id = st.store_cd 
   AND dis.reg_cd   = reg.reg_cd
   AND eff_dt       <= :dt
   AND (xpir_dt     is null OR xpir_dt > :dt)

CodePudding user response：

Try the below using PIVOT:

SELECT *
  FROM (SELECT pr.prod_id AS pr_id, pr.PROD_NAME, d.DISCOUNT AS disc
          FROM products pr JOIN discounts d ON pr.prod_id = d.prod_id) PIVOT (count (
                                                                                 pr_id)
                                                                                AS tw
                                                                          FOR (
                                                                             disc)
                                                                          IN ('2%',
                                                                             '10%',
                                                                             '8%',
                                                                             '3%',
                                                                             '5%'))