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: stackoverflow.com/questions/2852708/… OR stackoverflow.com/questions/4336985/…. 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
– Edper, Commented Apr 14, 2013 at 8:58

Taryn · Accepted Answer · 2013-04-14 15:41:05Z

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.
Awesome trick but with big table with a lot of data, it's too heavy :/
@Thermech I agree, with a large amount of data there are different ways that this could be done.

druud62 · Accepted Answer · 2018-07-26 06:58:35Z

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 */ ;

slfan · Accepted Answer · 2020-01-02 14:46:26Z

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

Collectives™ on Stack Overflow

Crosstab View in mySQL?

Clients

Scores

3 Answers 3

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Clients

Scores

3 Answers 3

3 Comments

Comments

Comments

Linked

Related