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.

Try to check this links: http://stackoverflow.com/questions/2852708/converting-mysql-resultset-from-rows-to-columns OR http://stackoverflow.com/questions/4336985/can-a-mysql-query-turn-rows-into-columns. The first one is good if the row-to-column values are known and the second one is you use GROUP_CONCAT but it will not make your rows into column actually. — Edper, Apr 14 '13 at 08:58

score 6 · Answer 1 · answered Apr 14 '13 at 15:41

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.

I was thinking more in terms of a view. May be there could be a procedure that deletes a view and recreates it view when a new client is added. — cjv, Apr 15 '13 at 05:41
Awesome trick but with big table with a lot of data, it's too heavy :/ — Thermech, Oct 29 '13 at 15:57
@Thermech I agree, with a large amount of data there are different ways that this could be done. — Taryn, Oct 29 '13 at 16:01

druud62 · Answer 2 · 2018-07-26T06:58:35.340

For a quick 'crosstab' overview of the logins per hour, I use:

SELECT _d
, GROUP_CONCAT(LPAD(_cnt,4," ") ORDER BY _h SEPARATOR " ") AS `. 00   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23` 
FROM (
  SELECT FROM_UNIXTIME(last_access_epoch,"%Y-%m-%d") AS _d
  , FROM_UNIXTIME(last_access_epoch,"%H") AS _h
  , COUNT(*) AS _cnt 
  FROM login_log 
  WHERE last_access_epoch < 3600*(@@timestamp DIV 3600) /* before current hour */
  GROUP BY _d, _h
) AS t 
GROUP BY _d
HAVING MIN(_h)="00"  /* only full days */
;

score 0 · Answer 3 · edited Jan 02 '20 at 14:46

0

If you have a large amount of data for the GROUP_CONCAT example above, you may need to change your group_concat_max_len to avoid a syntax error due to truncation.

SET SESSION group_concat_max_len=4096;

See: Trouble with GROUP_CONCAT and Longtext in MySQL

edited Jan 02 '20 at 14:46

slfan

8,950
115
65
78

answered Jan 02 '20 at 14:29

CattailNu

1

Crosstab View in mySQL?

Clients

Scores

3 Answers3

Linked