0
Follow
2
View

How to make numerous columns into rows

zcju001 注册会员
2023-01-25 10:52

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.