MySQL how to form query

Question

I have 2 mysql tables I'm working with.

Table 1 has employee information columns :

employee_number, date_of_birth, name, address, etc.

Table 2 has pay information columns : employee_number (foreign key), date, bonus

My boss has requested a screen to show the data like, with dates listed horizontally across the top and employee listed vertically :

        1/1/2000 | 1/1/2001 | 1/1/2002 | 1/1/2003
Bill    $500     | $600     | $700     | $900
Ferdie  $300     | $500     | $800     | $434
Tony    $450     | $234     | $432     | $343

What is the easiest way to format my query so that the dataset is in this format?

Answer 1

What you need is a pivot table.

MySQL does not have a built in way to create a pivot table, but you can create it "by hand":

SQL Fiddle

MySQL 5.6 Schema Setup:

create table employees(
  employee_number int primary key,
  name varchar(50)
);

create table payments(
  employee_number int,
  bonus_date date,
  bonus decimal(8,2)
);

insert into employees values
(1, 'Bill'), (2, 'Freddie'), (3, 'Tony');

insert into payments values
(1, '2000-1-1',500),(1, '2001-1-1',600),(1, '2002-1-1',700),(1, '2003-1-1',900),
(2, '2000-1-1',300),(2, '2001-1-1',500),(2, '2002-1-1',800),(2, '2003-1-1',434),
(3, '2000-1-1',450),(3, '2001-1-1',234),(3, '2002-1-1',432),(3, '2003-1-1',343);

Query 1:

-- 1. Prepare the column definition
select group_concat(distinct
           concat(
               "sum(case when bonus_date='", bonus_date, "' then bonus else 0 end) as `", bonus_date, "`"
           )
       )
into @sql
from payments

Results: No results

Query 2:

-- 2. Write the full query
set @sql = concat("select e.employee_number, e.name, ", @sql, " from employees as e inner join payments as p on e.employee_number = p.employee_number group by e.employee_number")

Results: No results

Query 3:

-- 3. Check the generated query (optional)
-- select @sql

Results: (uncomment the above query and check the result)

Query 4:

-- 4. Create a prepared statement using the query you've just created
prepare stmt from @sql

Results: No results

Query 5:

-- 5. Execute the prepared statement
execute stmt

Results:

| employee_number |    name | 2000-01-01 | 2001-01-01 | 2002-01-01 | 2003-01-01 |
|-----------------|---------|------------|------------|------------|------------|
|               1 |    Bill |        500 |        600 |        700 |        900 |
|               2 | Freddie |        300 |        500 |        800 |        434 |
|               3 |    Tony |        450 |        234 |        432 |        343 |

Query 6:

-- 6. When you're done, deallocate the prepared statement
deallocate prepare stmt

Results: No results

---

You may want to check my answer on a similar question.

Hope this helps.