how to make a new table from an existing table and add new column in spreadsheet

Question

I want to Ask about spreadsheet or google-sheets formula. I have a data that looks like this:

   A       B           C          D
   ------------------------------------------------
1 | UserId  fruitType   media     PriceStatus
2 | 3       Apple       Bag       Paid
3 | 7       Banana      Bag       Paid
4 | 7       Apple       Bag       Paid
5 | 43      Banana      Bag       Paid
6 | 43      Apple       Bag       FREE
7 | 43      Apple       Cart      Credit

Note:

1. My data only consist of 2 type of fruit : Apple and banana
2. 2 type of media : Bag and Cart
3. 3 Type of PriceStatus : Paid, Credit, & Free

As you can see the column is the same as the one in spreadsheet that I give value A-D and the row as 1-7, My plan is to make the sheets look like this:

UserId  Apple   Banana
3          1       0
7          1       1 
43         2       1

is it possible to make a new table like this in spreadsheet? I tried using VLOOKUP but still failed to implement it, can someone help me with this?

Answer 1

try:

=QUERY(A1:D, "select A,count(A) where A is not null group by A pivot B", 1)