How to convert and save dynamic rows into dynamic columns

Question

I have a table called events where I save every event that occurs on the website. Fiddle and code I've tried

+====+==========+=========+===============+=========+=====================+
| id | type     | user_id | website       | data    | created_at          |
+====+==========+=========+===============+=========+=====================+
| 1  | pageview | 1       | example.com   | /       | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 2  | pageview | 2       | example.com   | /path   | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 3  | purchase | 3       | example.com   | 2222    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 4  | view     | 3       | example.com   | product | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 5  | click    | 3       | example.com   | card    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 6  | pageview | 4       | site.com      | /       | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 7  | click    | 4       | site.com      | product | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 8  | custom   | 5       | example1.com  | test    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 9  | custom2  | 6       | google.com    | test    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 10 | custom3  | 5       | example11.com | test    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+

I have dynamic table in front and I want to have dynamic columns in it, like this.

+==========+=========+==========+=========+=========+=========+=========+=========+
| name     | ...     | event 1  | event 2 | event 3 | event 4 | event 5 | ...     |
+==========+=========+==========+=========+=========+=========+=========+=========+
| test     | ...     | 5        | 50      | 5       | 76      | 23      | ...     |
+----------+---------+----------+---------+---------+---------+---------+---------+
| test2    | ...     | 1        | 78      | 25      | 88      | 54      | ...     |
+----------+---------+----------+---------+---------+---------+---------+---------+

Where event1, event2...are dynamic columns from the events.type column.

I'm looking for the best way to organize and aggregate data in the events table.

I've written the query to convert the rows into columns and display the data the way I want, but the problem is saving this aggregated data in a way that I can filter it later.

• events A type column in a table can contain thousands of DISTINCT values
• I have to aggregate the data for each user. For example: User 1 may only have pageview, purchase... events, User 2 may have purchase, testevent1... events, So there will be different columns in front.

Any suggestions on how to aggregate the events data and save it in another table so I can run different queries in it later?

The fiddle and code I tried

Any help would be greatly appreciated.

Answer 1

In SQL, you cannot create a table with different columns for each row. There is no such thing as a "dynamic column".

One of the basic facts of how relational databases work is that a table has a header that names the columns and data types, followed by a set of rows where each row in the table has exactly the same columns as the header. That table.

So the best thing you can do is make a table with a column for each event type, even if that event type is unavailable to some users. The value in this column can be NULL, indicating that it is not relevant to this user.

In a SQL SELECT query, you must specify all columns in the query. These columns must be fixed in the query before it is parsed and before execution begins. You cannot create a query that dynamically adds columns while examining data during query execution.

So you have one type of pivot query:

SELECT user_id,
 COUNT(CASE type WHEN 'pageview' THEN 1 END) AS `pageview`,
 COUNT(CASE type WHEN 'purchase' THEN 1 END) AS `purchase`,
 COUNT(CASE type WHEN 'view' THEN 1 END) AS `view`,
 COUNT(CASE type WHEN 'click' THEN 1 END) AS `click`,
 ...more column expressions until you account for all possible types...
FROM events
GROUP BY user_id;

Before writing this query, you must understand all possible event types. You can use another query to do this:

SELECT DISTINCT type FROM events;