How to turn distinct products into columns with the count as value

Question

I have a table with customersID's and Products they have bought:

CustomerId	Product
3	Apple
3	Orange
3	Banana
7	Orange
7	Orange
7	Banana
9	Apple
9	Apple
9	Banana

I would like to create a column for each customer, with the amount of products they have bought of each distinct value:

CustomerId	Apple	Orange	Banana
3	1	1	1
7	0	2	1
9	2	0	1

For whatever reason, I'm having trouble finding a similar question online, which I suspect is due to my inability to formulate the question.

Answer 1

Today I learned about dynamic PIVOT queries - transform columns into rows. Combined with dynamic queries (queries whose content is not fully known until runtime), you can use PIVOT queries without knowing all the values in your column, by adding the values to the query during runtime.

Taking my question as an example:

1. Create and populate the first table in the question, which we will call Sales:

    DROP TABLE IF EXISTS #Sales
    CREATE TABLE #Sales
    (
    CustomerID INT,
    Product      NVARCHAR(255) NOT NULL
    );
    INSERT #Sales VALUES
    (3, N'Apple'),
    (3, N'Orange'),
    (3, N'Banana'),
    (7, N'Orange'),
    (7, N'Orange'),
    (7, N'Banana'),
    (9, N'Apple'),
    (9, N'Apple'),
    (9, N'Banana');
   

2. Group by CustomerID and Product to count the amount bought of each product:

    SELECT CustomerID
           , Product
           , COUNT(*) as Total_bought
    INTO #SalesSummed
    FROM #Sales
    GROUP BY CustomerID, Product
   

3. Add each distinct Product to the pivot query:

    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
    SET @columns = N'';
    SELECT @columns += N', p.' + QUOTENAME(Product)
    FROM (SELECT p.Product FROM #SalesSummed AS p
    GROUP BY p.Product) AS x;
    SET @sql = N'
    SELECT p.CustomerID, ' + STUFF(@columns, 1, 2, '') + '
    INTO #SalesMatrix
    FROM
    (
        SELECT p.CustomerID, p.Product, p.Total_bought
        FROM #SalesSummed AS p
    ) AS j
    PIVOT
    (
        SUM(Total_bought) FOR Product IN ('
        + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
        + ')
    ) AS p;';
    PRINT @sql;
    EXEC sp_executesql @sql;
   

Step 3 yields the table SalesMatrix:

CustomerID	Apple	Banana	Orange
3	1	1	1
7	NULL	1	2
9	2	1	NULL

Which is exactly what we wanted (we can replace NULL with 0)!