Google spreadsheet pivot table and join

Question

My concern is about transforming an array to another one :
I have a table like this :

Name?Val 1? Val 2
Joe ? ?A ?? B
Karl? ??? ?C
Joe ? ?C ? ?B

I would like this table as result:

Name A?B?C
Joe? X?X?X
Karl?? ? ?X

I'm lost, I tried using query or ArrayFormula but I failed, if someone can solves this ..?

Thanks a lot

question from:https://stackoverflow.com/questions/65601057/google-spreadsheet-pivot-table-and-join

Answer 1

use:

=INDEX(REGEXREPLACE(""&QUERY(IFERROR(SPLIT(FLATTEN(
 IF(B2:C="",,A2:A&"×"&B2:C)), "×")), 
 "select Col1,count(Col1) 
  where Col2 is not null 
  group by Col1 
  pivot Col2 
  label Col1'Name'"), "d+", "X"))