Question: 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.

Answer: 
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;

```
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

```