Switch rows to columns to generate report - MySql

Question

I have a simple database (using MySql) with which I need to generate a report.

I have a table that contains weekly logs which are submitted by users. Each of these logs have states, such as: submitted, approved, etc.

LogSubmissions Table
+----+--------+--------+---------+-------+
| id | weekId | userId | info... | state |
+----+--------+--------+---------+-------+
|  0 |      0 |     1  | data... |     0 |
|  1 |      0 |     2  | data... |     1 |
|  1 |      0 |     3  | data... |     1 |
|  2 |      1 |     1  | data... |     0 |

I also have a table which keeps track of the weeks.

WeekManager Table
+----+-----------+---------+---------+
| id | startDate | EndDate | ...     |
+----+-----------+---------+---------+
|  0 | Date1     | Date2   | ...     |
|  1 | Date3     | Date4   | ...     |
|  2 | Date5     | Date6   | ...     |

I need a report which displays the weeks as columns as well as the names from a user table. It should display the state of the weekly log the user submitted. For example Bob submission for Week 1 has a state of 0.

Report
+------+--------+--------+--------+-------+
| Name | Week 1 | Week 2 | Week 3 | ...   |
+------+--------+--------+--------+-------+
| Bob  |      0 |      1 |      0 | ...   |
| Joe  |      1 |      1 |      1 | ...   |
| Jim  |      0 |      0 |      0 | ...   |

I am having a bit of trouble getting this report working. My major obstacle is that weeks are added, so the amount of columns for report will vary.

This here helped quite a bit, however it only deals with a static amount of rows. I need something that varies depending on the amount of weeks. I was thinking of using a loop of some kind to achieve this, however I'm not sure if that is possible.

Any help would be appreciated, thank you.

User Table:

User Table
+----+------+---------+
| id | name | ...     |
+----+------+---------+
|  0 | ...  | ...     |
|  1 | ...  | ...     |
|  2 | ...  | ...     |

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

Sounds like you're trying to convert a horizontal table into a vertical one (temp table if you will). Use union all do this as per this previous post: SQL query ; horizontal to vertical .

edited May 23 '17 at 11:48

Community

1
1

answered Feb 07 '13 at 17:42

RandomUs1r

4,010
1
24
44

Taryn · Accepted Answer · 2013-02-07T18:03:48.380

Your table structure is not exactly clear (for example I don't see where you get user name).

The basic set up for a pivot in MySQL uses an aggregate function and a CASE expression similar to this:

select 
  l.userid,
  max(case when w.id =0 then l.state end) Week1,
  max(case when w.id =1 then l.state end) Week2,
  max(case when w.id =2 then l.state end) Week3
from LogSubmissions l
left join WeekManager w
  on w.id = l.weekid
group by l.userid

See SQL Fiddle with Demo.

Edit, if you have an unknown number of weeks then you can use a prepared statement to generate dynamic sql:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when w.id = ',
      id,
      ' then l.state end) AS Week',
      id + 1
    )
  ) INTO @sql
FROM  WeekManager;

SET @sql = CONCAT('SELECT l.userid, ', @sql, ' 
                  from LogSubmissions l
                  left join WeekManager w
                    on w.id = l.weekid
                  group by l.userid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Thanks for you help. However weeks will be added, I need a query that will create columns based on the number of weeks. — computer10171, Feb 07 '13 at 17:55
@computer10171 see my edit, I included a dynamic version for unknown data. — Taryn, Feb 07 '13 at 18:04

Switch rows to columns to generate report - MySql

2 Answers2