Join 2 tables to produce desired output

Question

I have 2 tables as following:

Products table:

ProductID   Name
   1        Condensed cheese
   2        Milk

Prices table:

ProductID   Currency   Price
    2          EUR       1.50
    2          USD       1.74
    2          JPY     194.624
    1          EUR       0.99
    1          USD       1.15

I am learning SQL and wondering what would be SQL statement to join 2 above tables to produce this output:

ProductID     Name               EUR     USD     JPY
    1         Condensed cheese   0.99    1.15    NULL
    2         Milk               1.50    1.74    194.624

Organizing data like that is typically called 'pivoting'. – Ruud Helderman Oct 13 '18 at 18:30 — Ruud Helderman, Oct 13 '18 at 18:30

score 2 · Answer 1 · answered Oct 13 '18 at 18:23

2

you can use max() function with case when

select t1.ProductID ,t1.Name,
 max(case when t2.Currenc= 'EUR' then Price end) as EUR,
 max(case when t2.Currenc= 'USD' then Price end) as USD,
 max(case when t2.Currenc= 'JPY' then Price end) as JPY
 from 
Products t1 join Prices  t2 on t1.ProductID =t2.ProductID  
group by t1.ProductID ,t1.Name

answered Oct 13 '18 at 18:23

Zaynul Abadin Tuhin

31,407
5
33
63

Thank you for your answer and sorry for not being able to accept more than 1 answer. Madhur Bhaiya answer was more helpful with explanation, I upvoted your answer! – hoangfin Oct 14 '18 at 18:51
1

@meteorzeroo thanks you can accept any one's answer which you think better . but the answer you accepted is not ansi sql where my answer will fit for any db which is followed by ansi sql. aggregate function not work for NULL but he mentioned that :) you may follow him if you think that is better – Zaynul Abadin Tuhin Oct 15 '18 at 06:22

score 0 · Accepted Answer · answered Oct 13 '18 at 19:16

It is a Pivot Table problem. You will need to use conditional aggregation with Group By clause.

Do an Inner Join between the two tables using ProductID.
We do a Group By on ProductId and Name, since you want a single row for a productid with all the prices in the same row.
Now, we will use conditional function If() to determine price for a specific currency column. If the currency code matches for that column, we consider that price value, else we consider null. So, for example, in a column aliased EUR, we will have null values for rest of the currencies (except EUR). We will then use Max() function to ensure that we consider the corresponding currency price only.
If there is no price value for a specific currency in the Prices table, it shall come as null value (all currencies will show null and Max(null, null, ...) = null
Eventually we Order By ProductID ASC to get the result sorted in ascending order by ProductID.

Try the following query:

SELECT pdt.ProductID, 
       pdt.Name, 
       MAX( IF(prc.Currency = 'EUR', prc.Price, NULL) ) AS EUR, 
       MAX( IF(prc.Currency = 'USD', prc.Price, NULL) ) AS USD, 
       MAX( IF(prc.Currency = 'JPY', prc.Price, NULL) ) AS JPY
FROM Products AS pdt 
INNER JOIN Prices AS prc ON prc.ProductID = pdt.ProductID 
GROUP BY pdt.ProductID, pdt.Name 
ORDER BY pdt.ProductID ASC

Join 2 tables to produce desired output

2 Answers2