Pivot table with MySQL

Question

I'm trying to create a pivot table like output (cross table? Not entirely sure about the difference). The idea is to have rows, columns and summary values (specifically: distinct count). The data looks like this:

Transactions table:

product_id seller_id seller_country buyer_id

buyers table

buyer_id buyer_country

There could be multiple rows for the same product, and the same buyer can buy from multiple sellers multiple times.

Buyer table has unique rows for each buyer, so while the same buyer can appear on multiple rows in the transactions table, he'll appear just on one row in the buyers table.

What I want to create is a matrix where the rows are for sellers' countries, columns for buyers' countries, and the values in the table count the number of unique buyers from each country who bought from each country. Count the distinct buyers each cell.

I tried several things but ultimately couldn't get past a row group by. Searched online for solutions but nothing was relevant for this very problem. This is what I managed to do with MS access:

TRANSFORM Count(Dummy_users.buyer_id) AS CountOfbuyer_id
SELECT Dummy_transactions.seller_country
FROM Dummy_users LEFT OUTER JOIN Dummy_transactions ON Dummy_users.buyer_id = Dummy_transactions.buyer_id
GROUP BY Dummy_transactions.seller_country
PIVOT Dummy_users.buyer_country;

but I don't see PIVOT is acceptable anywhere in mysql (no search results which offer that solution), and also for some reason it broke when I tried to add the distinct element to the count.

Can you help me with the syntax please?

Here is some dummy data if needed: transactions: https://docs.google.com/spreadsheets/d/1vHzjmSkaQ5G5lTdV_M6h7gp8zR4Kfqz70gL4TeID2jQ/ buyers: https://docs.google.com/spreadsheets/d/18HXZ_GotqEMDhxytYKxNeD4W6-PfqUvkcWh3DBuCYPA/

Many thanks!

Answer 1

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;


SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)