SQL pivot with grouping

Question

I have a query that now returns 2 rows, while I need it to return 1 row. It looks like this:

Initial table:

CREATE TABLE a (
  product VARCHAR(30) ,
  fee_type VARCHAR(30) ,
  vendor VARCHAR(30)
);

INSERT INTO a
    (product, fee_type, vendor)
VALUES 
    ('ProductA', 'fee_typeX', 'X'),
    ('ProductA', 'fee_typeY', 'Y');

Query:

select product,
(case when fee_type= 'fee_typeX' then vendor else null end) as vendorX, 
(case when fee_type = 'fee_typeY' then vendor else null end) as vendorY
from a

got this:

product	vendorX	vendorY
ProductA	X	null
ProductA	null	Y

but need this:

product	vendorX	vendorY
ProductA	X	Y

'group by product' leads to VendorY data loosing.

Any help would be appreciated.

Answer 1

As per your query, you are currently selecting the "product" field and two more fields, which evaluate to NULL when the "fee_type" changes. In order to get a single row for each of your products, you need to apply an aggregation over your values extracted with the CASE.

select product,
       MAX(case when fee_type= 'fee_typeX' then vendor end) as vendorX, 
       MAX(case when fee_type = 'fee_typeY' then vendor end) as vendorY
from a
GROUP BY product

Side note: you can avoid ELSE NULL inside the CASE statement. They're evaluated to NULL by default if not specified.