Pivot Table With 3 tables

问题:

I'm trying to generate a pivot view from three tables:

1. students
2. fees
3. stud_fee(relation table)

The tables:

Students Table
+----+-----------+-----------+----------------+----------------+-------+
| id | school_id | last_name | first_name     | middle_initial | yrlvl |
+----+-----------+-----------+----------------+----------------+-------+
|  1 | 2080295   | Doe       | John           | A              |     3 |
|  2 | 0239129   | Rizal     | Jose           | M              |     4 |
|  3 | 1231238   | Santos    | Jane           | M              |     2 |
+----+-----------+-----------+----------------+----------------+-------+

Fee table
+----+--------------------+------------+
| id | fee_name           | fee_amount |
+----+--------------------+------------+
|  1 | Registration Fee   |        100 |
|  2 | News Letter        |        100 |
|  3 | T-Shirt            |        250 |
|  4 | Party              |        500 |
+----+--------------------+------------+

stud_fee table
+----+------------+-----+
| id | stud_id | fee_id |
+----+---------+--------+
|  1 |       1 |      1 |
|  2 |       1 |      2 |
|  3 |       1 |      3 |
|  4 |       2 |      1 |
|  5 |       3 |      1 |
|  6 |       3 |      4 |
+----+---------+--------+

I would like to make the fee as the columns and students as the rows. I would like to make it display as:

+-----------+------------------+-------------+---------+-------+-------+
| school_id | Registration Fee | News Letter | T-Shirt | Party | Total |
+-----------+------------------+-------------+---------+-------+-------+
| 2080295   |              100 |         100 |     250 |       |   450 |
| 0239129   |              100 |             |         |       |   100 |
| 1231238   |              100 |             |         |   500 |   600 |
+-----------+------------------+-------------+---------+-------+-------+

回答1:

It looks like you might have an unknown number of fees that you want to turn into columns, if that is the case then you will want to use prepared statements to query this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when f.fee_name = ''',
      f.fee_name,
      ''' then f.fee_amount else 0 end) AS `',
      f.fee_name, '`'
    )
  ) INTO @sql
FROM fee f;

SET @sql = CONCAT('SELECT s.school_id, ', @sql, '
                    , sum(f.fee_amount) as Total
                  FROM students s
                  LEFT JOIN stud_fee sf
                    on s.id = sf.stud_id
                  LEFT JOIN fee f
                    on sf.fee_id = f.id
                   GROUP BY s.school_id');


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

See SQL Fiddle with Demo

回答2:

Try this:

SELECT 
  s.school_id,
  MAX(CASE WHEN f.fee_name = 'Registration Fee' THEN f.fee_amount END) 
    AS 'Registration Fee',
  MAX(CASE WHEN f.fee_name = 'News Letter'      THEN f.fee_amount END) 
    AS 'News Letter',
  MAX(CASE WHEN f.fee_name = 'T-Shirt'          THEN f.fee_amount END) 
    AS 'T-Shirt',
  MAX(CASE WHEN f.fee_name = 'Party'            THEN f.fee_amount END) 
    AS 'Party',
  SUM(f.fee_amount) AS Total
FROM Students s
INNER JOIN stud_fee sf 
        ON s.Id = sf.stud_id
INNER JOIN fee f       
       ON sf.fee_id = f.id
GROUP BY s.school_id

SQL Fiddle Demo