MySQL - Column to Row

aphrodite 0
Viewed 39

In the original problem, we have a table that stores the date and win/loss information for each game played by a team. the matches table

We can use the following SQL statements to get information about the number of games won and lost for each day.

SELECT match_date AS match_date, 
   SUM(IF(result = 'win',1,0)) AS win, 
   SUM(IF(result = 'lose',1,0)) AS lose
FROM matches
GROUP BY date;

We store the query results in the matches_2 table.the matches_2 table My question is, how can we get the matches table based on the matches_2 table with a query? In the simpler case, we can achieve the task of 'column to row' using union/union all. But that doesn't seem to work in this problem.

All relevant sql code can be found in the following fiddle： https://dbfiddle.uk/rM-4Y_YN

mysql
1 Answers

You can use recursive CTE for this:

WITH RECURSIVE wins (mdate, w) AS
(
  SELECT match_date as mdate, 1
  FROM matches
  WHERE win>0
  UNION ALL
  SELECT match_date, w + 1 FROM matches 
   JOIN wins on match_date=mdate
  WHERE w < win
),
losses (mdate, l) AS
(
  SELECT match_date as mdate, 1
  FROM matches
  WHERE lose>0
  UNION ALL
  SELECT match_date, l + 1 FROM matches 
   JOIN losses on match_date=mdate
  WHERE l < lose
)
SELECT mdate as match_date, 'lose' FROM losses
UNION ALL
SELECT mdate as match_date, 'win' FROM wins

See a db-fiddle

slaakso0
Related