0

I could really use some help with the problem I'm facing. I'm trying to dynamically add columns to a query based on the number of times a user_id occurs in this example event_table.

So, this is the example event_table. Every time the user_id comes up, I want another column with CONCAT("event_", times_occured) added to the result.

id user_id create_date
1 344 2021-05-25
2 25 2021-05-25
3 344 2021-07-06
4 344 2021-07-07
5 3245 2021-08-25
6 52 2021-09-14
7 52 2021-10-11

The query result should be formed this way.

user_id event_1 event_2 event_3
25 2021-05-25 null null
52 2021-09-14 2021-10-11 null
344 2021-05-25 2021-07-06 2021-07-07
3245 2021-08-25 null null

I'm not sure if this is possible, and if it is, do I need to use recursion or loops?

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Dylan Brugman
  • 11
  • 1
  • Which versions of mysql and mariadb are you on? – P.Salmon Dec 01 '21 at 12:09
  • `SELECT user_id, GROUP_CONCAT(`event`) FROM table GROUP BY user_id`. This will not add a column, but will create a comma-separated list in the second column with all te dates. (See: [group_concat()](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat)) – Luuk Dec 01 '21 at 12:14
  • You could `GROUP BY user_id` and then select [GROUP_CONCAT(create_date SEPARATOR ',')](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat). That way you get all the dates in one result column, but they can be easily seperated by the comma. It all depends on what you're actually doing with this. – KIKO Software Dec 01 '21 at 12:17
  • This cannot be performed in a query. You need in stored procedure with dynamic SQL usasge for to solve your task. – Akina Dec 01 '21 at 12:27
  • @Luuk and KIKO Software Thanks a lot! That was exactly what I was looking for. I'm using this to analyse some data using Excel. And I needed it in a csv form anyway! – Dylan Brugman Dec 01 '21 at 19:10
  • Just one addition on the question. It's not necessary but makes it a bit easier to work with; Can I create the header also using Concat with the events numbered from 1 to the max amount of occurences? – Dylan Brugman Dec 01 '21 at 19:17
  • @DylanBrugman: short answer: When you use the function `GROUP_CONCAT()` you will only get 2 columns (according to MySQL). when you start counting the `,` then, according to CSV you will have more columns in the rows. Other answer: It can be done when you use dynamic SQL, like Akina suggested. see: [How To have Dynamic SQL in MySQL Stored Procedure](https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) – Luuk Dec 02 '21 at 12:41

0 Answers0