How to count unique values on one column without double values from another column-CodePudding

My very first question as a newb in SQL.

I want to count unique values from one column Transport, group them by ID and delete double values in the Transport column that may be caused by Product column. Could be very simple, but at this point I need another point of view.

This is the data

ID	Product	Transport
1	A	Plane
1	B	Plane
2	A	Train
2	B	Train
2	C	Ship
3	A	Plane
3	B	Train
3	C	Ship
3	D	Ship

I would want to have the ID as unique values and then count each of the unique values of the Transport. If I do it with a normal GROUP BY, the Products will double the counting.

The result I need has to count each of the Transport values in separated columns without being doubled by the Product column. So it should look something like:

ID	Plane	Train	Ship
1	1	0	0
2	0	1	0
3	1	1	1

I think it's simple but maybe I'm missing something. Any help would be appreciated!

Thank you.

CodePudding user response：

You can get a pivot by combining CASE with MAX(), as in:

select
  id,
  max(case when transport = 'Plane' then 1 else 0 end) as plance,
  max(case when transport = 'Train' then 1 else 0 end) as train,
  max(case when transport = 'Ship' then 1 else 0 end) as ship
from t
group by id

CodePudding user response：

Just adding something to @The Impater's result

SELECT
  id,
  MAX(transport = 'Plane') AS plance,
  MAX(transport = 'Train') AS train,
  MAX(transport = 'Ship')  AS ship
FROM `test_table`
GROUP BY id

I was taught there is no need to assign 1 and 0 when it can be done via boolean-type logic as results are returned either in 0 or 1.