Crosstab View in mySQL?

mysql crosstab query dynamic columns
mysql crosstab query example
how to display row values as columns in mysql
mysql columns to rows
mysql pivot table vertical data to horizontal
transpose in mysql query
mysql transpose rows to columns
sqlite crosstab

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.

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.

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� Intelligence that Learns App Patterns & Adapts to Maximize Data Protection.

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

MySQL Crosstab in a view, Part of your problem is your table structure. Unfortunately MySQL does not have a PIVOT/UNPIVOT function so you will need to do this with a UNION ALL and� Like the SELECT query, a crosstab query retrieves information from our database tables and presents it. However, the results of the crosstab query are displayed more like a spreadsheet, with row headers and column headers. Let’s see how we can use a crosstab query to get data in the crosstab results. Example

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

CROSS TAB QUERY IN MYSQL, MySQL Multi-Aggregated Rows in Crosstab Queries MySQL's alter the months CASE statement, because rows which display the yearly totals� I was wondering if there was an easy way to do a crosstab in SQL server if you don't know all of the possible values for the crosstab column headers, or if there are a large number of them? For example, I have a list of people, and I want to show an aggregate value (let's say sales) for each person, for each month spanning several years.

Creating a cross tab in MySQL (Example), mysql. Data stored in a database is often also useful for statistical purposes. If you own a web-shop you want to be able to create a report about� Crosstab in Mysql View as plain text Hi guys: I am quite new in SQL and I need to build a crosstab based in two tables using Mysql and PHP, but it is becoming quite dificult.

All About the Crosstab Query, A cross tabulation query, or cross tab for short, is used to display the Although I will be working in MySQL, the techniques discussed here� Crosstab View in mySQL? Ask Question Asked 7 years, 3 months ago. Active 6 months ago. Viewed 13k times 2. I dont know I titled this correct, but let me explain what

[Edit Pending]Efficient Cross Tab Query in MySQL, Cross Tab View: Let's assume you want to build a view which looks like for Q1 and Q2, now how will you transform this into a MySQL query? Creating a cross tab in MySQL. mysql. Data stored in a database is often also useful for statistical purposes. If you own a web-shop you want to be able to create a

Comments
  • 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.
  • 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.