AWS Athena create a binary matrix

Question

Is it possible to create a binary matrix in AWS Athena from a table. For example we have the following table:

name	product
John	Bike
John	Shirt
John	Ball
Blake	Shirt
Mike	Ball
Mike	Hat

To be converted to the following:

name	Bike	Shirt	Ball	Hat
John	1	1	1	0
Blake	0	1	0	0
Mike	0	0	1	1

if thenumber of items is fixed, you can do pivot https://stackoverflow.com/questions/48013254/how-to-pivot-rows-into-columns-in-aws-athena — nbk, Aug 16 '22 at 16:08
as you can read in the thread, building a dynamic sql approach will be time consuming, but you can try it — nbk, Aug 16 '22 at 16:27

UiraSiqueira · Answer 1 · 2022-09-07T09:23:48.793

1

I suggest you to use Case expression:

select name, 
sum(case when product = 'bike' then 1 else 0 end) as "Bike",
sum(case when product = 'Shirt' then 1 else 0 end) as "Shirt",
sum(case when product = 'Ball' then 1 else 0 end) as "Ball",
sum(case when product = 'Hat' then 1 else 0 end) as "Hat"
from tableName

edited Sep 07 '22 at 09:23

answered Sep 07 '22 at 09:22

UiraSiqueira

11
2

AWS Athena create a binary matrix

1 Answers1