join two tables and generate two columns(pivot) depending on value in one column

Question

I have two tables named say Account table and Product table.

The records of two tables are given below:

Accounts Table:

Product Table:

account_id  date  product
1           2015    A
1           2016    B
2           2012    B
2           2013    A
3           2017    A

Expected Result:

id     isA    isB

1      Yes    No
2      Yes    Yes
3      No     No

I want to get result as shown in the example (product as columns) for the given predicate(date) say (less than 2016). If any product is not present for an id or it does not satify date condition then it will have 'No' value for that product column. for example for account_id=3 date is 2017 which does not match our predicate hence the value of isA is No. Similarly for account_id=3 we do not have product B entry. So isB column should also have No value.

Currently i am getting two records for ids which have entries for both products. Is there a way to somehow merge those rows.

score 2 · Accepted Answer · edited Mar 28 '16 at 19:06

2

Obviously you get all data needed from the product table; the accounts table is not needed. Group by account number and see if you have matches.

SELECT 
  account_id,
  MAX(CASE WHEN product = 'A' AND date < 2016 THEN 'yes' ELSE 'no' END) AS isa,
  MAX(CASE WHEN product = 'B' AND date < 2016 THEN 'yes' ELSE 'no' END) AS isb
  FROM product
GROUP BY account_id;

edited Mar 28 '16 at 19:06

chancrovsky

582
3
11

answered Mar 28 '16 at 17:29

Thorsten Kettner

89,309
7
49
73

Hi Thorsten, thanks for the solution. I also checked http://stackoverflow.com/questions/6353427/using-max-in-varchar-field to get better understanding of aggregate functions on strings. – user2406618 Mar 28 '16 at 18:20

score 0 · Answer 2 · answered Mar 28 '16 at 18:19

0

To complete @Thorsten Kettner's answer, another CASE is needed.
suppose you want to show 'zNo' word instead of 'no' word, in this case MAX function doesn't return proper result.

SELECT 
  account_id,
  CASE WHEN(MAX(CASE WHEN product = 'A' AND DATE < 2016 THEN 1 ELSE 0 END) = 1) THEN 'Yes' ELSE 'No' END as isA,
  CASE WHEN(MAX(CASE WHEN product = 'B' AND DATE < 2016 THEN 1 ELSE 0 END) = 1) THEN 'Yes' ELSE 'No' END as isB
FROM Product
GROUP BY account_id

answered Mar 28 '16 at 18:19

Siyavash Hamdi

2,764
2
21
32

You are wrong, this is not needed. `MAX('no','no','no') = 'no'` and `MAX('no','yes','no') = 'yes'`. But your approach is also valid, and some people may even find it easier to read. – Thorsten Kettner Mar 28 '16 at 20:21
So if you want to show 'zNo' instead of 'No', which approach should be considered? – Siyavash Hamdi Mar 28 '16 at 20:27
Then `MIN(CASE WHEN product = 'A' AND date < 2016 THEN 'yes' ELSE 'no' END) AS isa`. (Same with 'ja' and 'nein' in German.) – Thorsten Kettner Mar 28 '16 at 20:32
:-) Hmm, Yes that works, but I think this is not a general approach. – Siyavash Hamdi Mar 28 '16 at 20:40
I am pretty sure that all collations that contain latin characters order 'no' before 'yes' before 'zno', but yes there may be some border case with languages where certain character combinations get sorted elsewhere (like 'ch' and 'll' in Spanish). So yes, if you happen to run my query with a language setting where 'no' is regarded a special character coming after 'y', but you still want to see the English 'yes' and 'no' rather then their translations, then the query would fail. I doubt that such a language exists, but it is possible, and your numeric approach is thus a tad safer. – Thorsten Kettner Mar 28 '16 at 21:26

join two tables and generate two columns(pivot) depending on value in one column

2 Answers2