Crosstab View in mySQL?
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.
cjv
Updated on June 13, 2022Comments
-
cjv almost 2 years
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.