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?

score 2 · Accepted Answer · edited May 23 '17 at 12:14

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.

@Viking Happy to help. The construction of pivot tables in MySQL is quite elaborated, but it works quite well. By the way, if this answer solves your problem, mark it as accepted ;) — Barranka, Sep 23 '15 at 23:22

MySQL how to form query

1 Answers1