How to make numerous columns into rows

Question

table like above

I want to make it like this.

The presented table is a table in which the user ID and access time are accumulated each time a user accesses.

With this table, from the first date to the present, I would like to make O if the user accessed each day, and X if not.

At first, I thought I could use CASE WHEN because I had to simply make rows into columns, but it was difficult to write all the dates.

The next thing to consider was to make it a recursive CTE, but the recursive CTE does not become a JOIN but only a UNION.

(I'm a beginner, so I didn't know..)

Do I need to create a result value with a recursive CTE and convert it back to a row into a column?... I'm not sure...

How should I approach it?

I really appreciate it if you tell me if you can use any ideas or concepts without writing a query.

Have a nice day.

Answer 1

There are really not a lot of good ways to do this, and it is highly variable depending on your use case. mySQL sorely needs the pivot capabilities that are present in some other DBMSs. One way to do it is to use CONCAT and GROUP_CONCAT to generate the SQL dynamically, then execute it with a prepared statement. Take a look at this fiddle:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4697dadf0ebf2eaadcf98b4205b42f67

So first here is how to generate the statement with concat and GROUP_CONCAT:

select concat(
  'select userID, ',
  group_concat(distinct "sum(dt='", dt, "') as '", dt, "'" order by dt),
  ' from sample group by userID'
) from sample;

We're going to use GROUP_CONCAT with DISTINCT to generate a column for each distinct date. If the formatting bothers you it is possible to clean it up, adding line breaks and using ', ' as the separator in group_concat, but it won't make it run any better.

Once you can generate the statement, you need to get it into a variable, which is pretty simple:

select concat(
  'select userID, ',
  group_concat(distinct "sum(dt='", dt, "') as '", dt, "'" order by dt),
  ' from sample group by userID'
) into @stmt from sample;
select @stmt;

Then all you need to do is to create a prepared statement from that variable and execute it:

prepare stmt from @stmt;
execute stmt;

And optionally deallocate it, although it will automatically deallocate after the session ends, iirc.

deallocate prepare stmt;

This just generates 1s and 0s, but you could easily make it generate Xs and Os. I just figured this was an abstract simple example, not your actual use case.

Let me know if this helps you. Also, I am not 100% sure what kind of security issues you are going to open up with this. You are going to create dynamic SQL statements and execute them, so if there is a possibility that a malicious user can manipulate the data you will need to be careful. I don't think there's much danger in the datetime field, and the id is an int, so not much there, either, but again I don't know if this is your actual use case.