mysql pivot tables

Posted on   |   In   |  

Example 1

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT  
  IFNULL(empId,'Totals') AS EmpId,       -- outer query labels rollup row 
  sums.2005, sums.2006, sums.2007,       -- and calculates horizontal sums 
  sums.2005 + sums.2006 + sums.2007 AS Sums 
FROM (                                   -- inner query groups by employee 
  SELECT                                 -- with an expression for each column 
    EmpID, 
    SUM(IF(Yr=2005,sales,0)) As '2005', 
    SUM(IF(Yr=2006,sales,0)) As '2006', 
    SUM(IF(Yr=2007,sales,0)) As '2007' 
  FROM Sales 
  GROUP BY EmpID WITH ROLLUP 
) AS sums;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT  
  IfNull(Year,'Totals') Year,  
  Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, `Dec`,  
  Qty AS Count,  
  Yrly as 'Yrly Total' 
FROM ( 
  SELECT  
    year(orderdate) AS 'Year', 
    Round(Sum(CASE WHEN Month(orderdate)= 1 THEN amount ELSE 0 END),2) AS Jan, 
    Round(Sum(CASE WHEN Month(orderdate)= 2 THEN amount ELSE 0 END),2) AS Feb, 
    Round(Sum(CASE WHEN Month(orderdate)= 3 THEN amount ELSE 0 END),2) AS Mar, 
    Round(Sum(CASE WHEN Month(orderdate)= 4 THEN amount ELSE 0 END),2) AS Apr, 
    Round(Sum(CASE WHEN Month(orderdate)= 5 THEN amount ELSE 0 END),2) AS May, 
    Round(Sum(CASE WHEN Month(orderdate)= 6 THEN amount ELSE 0 END),2) AS Jun, 
    Round(Sum(CASE WHEN Month(orderdate)= 7 THEN amount ELSE 0 END),2) AS Jul, 
    Round(Sum(CASE WHEN Month(orderdate)= 8 THEN amount ELSE 0 END),2) AS Aug, 
    Round(Sum(CASE WHEN Month(orderdate)= 9 THEN amount ELSE 0 END),2) AS Sep, 
    Round(Sum(CASE WHEN Month(orderdate)=10 THEN amount ELSE 0 END),2) AS Oct, 
    Round(Sum(CASE WHEN Month(orderdate)=11 THEN amount ELSE 0 END),2) AS Nov, 
    Round(Sum(CASE WHEN Month(orderdate)=12 THEN amount ELSE 0 END),2) AS `Dec`, 
    Count(*) AS Qty, 
    Round(Sum(amount),2) AS Yrly 
  FROM orderhist 
  GROUP BY year  
  WITH ROLLUP 
) AS sums ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT  
  IfNull(Year,'Totals') Year,  
  Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, `Dec`,  
  quantity AS Count,  
  Yrly as 'Yrly Total' 
FROM ( 
  SELECT  
    year(orderdate) AS 'Year', 
    Round(Sum(If( Month(o.orderdate)= 1, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Jan, 
    Round(Sum(If( Month(o.orderdate)= 2, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Feb, 
    Round(Sum(If( Month(o.orderdate)= 3, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Mar, 
    Round(Sum(If( Month(o.orderdate)= 4, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Apr, 
    Round(Sum(If( Month(o.orderdate)= 5, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS May, 
    Round(Sum(If( Month(o.orderdate)= 6, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Jun, 
    Round(Sum(If( Month(o.orderdate)= 7, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Jul, 
    Round(Sum(If( Month(o.orderdate)= 8, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Aug, 
    Round(Sum(If( Month(o.orderdate)= 9, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Sep, 
    Round(Sum(If( Month(o.orderdate)=10, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Oct, 
    Round(Sum(If( Month(o.orderdate)=11, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Nov, 
    Round(Sum(If( Month(o.orderdate)=12, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS `Dec`, 
    Count(*) AS quantity, 
    Round(Sum((unitprice*quantity)-discount),2) AS Yrly 
  FROM orders o 
  JOIN orderdetails d USING(orderID) 
  GROUP BY year  
  WITH ROLLUP 
) AS sums ;

Example 2

table

1
2
3
4
5
6
7
8
9
10
11
12
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
|    1 | Bob  |    1 |    75 |
|    2 | Bob  |    2 |    77 |
|    3 | Bob  |    3 |    78 |
|    4 | Bob  |    4 |    80 |
|    5 | Sue  |    1 |    90 |
|    6 | Sue  |    2 |    97 |
|    7 | Sue  |    3 |    98 |
|    8 | Sue  |    4 |    99 |
+------+------+------+-------+

way-1

1
2
3
4
5
6
select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

way-2

1
2
3
4
5
6
SELECT name,
SUM(IF(exam=1,score,NULL)) AS exam1,
SUM(IF(exam=2,score,NULL)) AS exam2,
SUM(IF(exam=3,score,NULL)) AS exam3,
SUM(IF(exam=4,score,0)) AS exam4
FROM exams GROUP BY name;

result

1
2
3
4
5
6
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+

Example 3

mysql pivot-1
mysql pivot-2
mysql pivot-3

My Problem

Daily Customer Growth and Daily Customer Growth by Gender
mysql pivot-4

1
2
3
4
5
6
7
select date_format(SignedUp,'%Y%m%d') days, 
count(case when Gender = 'Male' then id else null end) as m,
count(case when Gender = 'Female' then id else null end )as f,
count(*) as total
from customerdatasource.customerdataset
group by days
order by days

mysql pivot-5

unsolved problem

  1. unpivot in mysql:
  2. Pivot table with dynamic columns in MySQL:http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/
  3. pivot function in SQL server: http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
    https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx.aspx)

    reference

    datacharmer.org/downloads/pivot_tables_mysql_5.pdf
    http://blog.csdn.net/redwood_lin/article/details/1550606
    http://www.artfulsoftware.com/infotree/qrytip.php?id=78