CODEWITHSUNDEEP
sql-servert-sql
user2406618
user2406618

Reputation: 144

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

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

The records of two tables are given below:

Accounts Table:

Id
1
2
3
4

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.

Upvotes: 1

Views: 54

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

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;

Upvotes: 2

Siyavash Hamdi
Siyavash Hamdi

Reputation: 3087

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

Upvotes: 0

Related Questions