sql - Crosstab View in mySQL?

Question

I dont know I titled this correct, but let me explain what i am looking for.

I have two tables.

Clients

clID   (primary key)
ClName (varchar)

Scores

ID     (Primay key)
clID   (F Key)
PlayDate  (Date/Time)
Score     (double)

---

Client table data looks like this

clID  clName
1     Chris
2     Gale
3     Donna

Scores table data looks like this

ID  clID  PlayDate    Score
1   2     23/01/2012  -0.0125
2   2     24/01/2012  0.1011
3   3     24/01/2012  0.0002
4   3     26/01/2012  -0.0056
5   3     27/01/2012  0.0001
6   1     12/01/2012  0.0122
7   1     13/01/2012  0.0053

---

Is it possible to create a view that will look like this

Date         Chris   Gale    Donna
12/01/2012   0.0122   -        -
13/01/2012   0.0053   -        -
23/01/2012     -     -0.0125   -
24/01/2012     -     0.1011  0.0002
26/01/2012     -        -    -0.0056
27/01/2012     -        -    0.0001

If later there is a another new client then i should be able to check the data for that new client in the new column that will be now created in this view.

Thanks in advance.

Answer 1

This type of data transformation is called a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.

If the names of the clients is known ahead of time, then you can hard-code the query:

select s.playdate,
  sum(case when clname = 'Chris' then score end) Chris,
  sum(case when clname = 'Gale' then score end) Gale,
  sum(case when clname = 'Donna' then score end) Donna
from clients c
inner join scores s
  on c.clid = s.clid
group by s.playdate;

See SQL Fiddle with Demo.

If you have an unknown number of clients or you will be adding new clients that you will want included without having to change the code, then you can use a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN clName = ''',
      clName,
      ''' THEN score else ''-'' END) AS `',
      clName, '`'
    )
  ) INTO @sql
FROM clients;

SET @sql 
  = CONCAT('SELECT s.playdate, ', @sql, ' 
            from clients c
            inner join scores s
              on c.clid = s.clid
            group by s.playdate');

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

See SQL Fiddle with Demo. Both queries will give the same result.