Help with a pivot table

[arenaninja]

Helloo everyone. I'm trying to create a pivot table from a table which handles different instrument rates depending on institution type. Here's the table structure:

+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| pkFeeSchdID  | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| fkEquipID    | int(10) unsigned | NO   |     | NULL              |                             |
| fkInstTypeID | int(10) unsigned | NO   |     | NULL              |                             |
| rate         | decimal(6,2)     | NO   |     | NULL              |                             |
| dateOfEffect | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+------------------+------+-----+-------------------+-----------------------------+

I'm pivoting the fkEquipID as Row headings and fkInstTypeID as Column heading. What I want is something like this (I'm just typing this manually):

+---------+-------+-------+
| equipid | Rate1 | Rate2 |
+---------+-------+-------+
|       2 | 22.85 | 34.62 |
|       3 | 22.85 | 34.62 |
|       1 | 22.85 | 34.62 |
|       4 | 32.50 | 68.50 |
+---------+-------+-------+

 

Here's my first attempt, and its result:

SELECT equip.equipid, 
CASE WHEN feeschedule.fkInstTypeID=1 THEN feeschedule.rate ELSE 0 END AS Rate1, 
CASE WHEN feeschedule.fkInstTypeID=2 THEN feeschedule.rate ELSE 0 END AS Rate2 
FROM feeschedule LEFT JOIN equip ON (feeschedule.fkEquipID=equip.equipid) LIMIT 10;
+---------+-------+-------+
| equipid | Rate1 | Rate2 |
+---------+-------+-------+
|       2 | 22.85 |     0 |
|       2 |     0 | 34.62 |
|       2 |     0 |     0 |
|       3 | 22.85 |     0 |
|       3 |     0 | 34.62 |
|       3 |     0 |     0 |
|       1 | 22.85 |     0 |
|       1 |     0 | 34.62 |
|       1 |     0 |     0 |
|       4 | 32.50 |     0 |
+---------+-------+-------+

I figured I could fix this by grouping, but alas no cigar:

SELECT equip.equipid, 
CASE WHEN feeschedule.fkInstTypeID=1 THEN feeschedule.rate ELSE 0 END AS Rate1, 
CASE WHEN feeschedule.fkInstTypeID=2 THEN feeschedule.rate ELSE 0 END AS Rate2 
FROM feeschedule INNER JOIN equip ON (feeschedule.fkEquipID=equip.equipid) GROUP BY equip.equipid LIMIT 10;
+---------+-------+-------+
| equipid | Rate1 | Rate2 |
+---------+-------+-------+
|       1 | 22.85 |  0.00 |
|       2 | 22.85 |  0.00 |
|       3 | 22.85 |  0.00 |
|       4 | 32.50 |  0.00 |
|       5 | 32.50 |  0.00 |
|       6 | 32.50 |  0.00 |
|      10 | 32.50 |  0.00 |
|      11 | 32.50 |  0.00 |
|      12 | 32.50 |  0.00 |
+---------+-------+-------+

It seems there's some subtlety that escapes me to give me the desired format. I'm still siphoning tons of documentation, any kind souls out there know the answer and feel like sharing?

[Barand]

is this what you want?

 

SELECT * FROM feeschedule;

+-------------+-----------+--------------+-------+
| pkFeeSchdID | fkEquipID | fkInstTypeID | rate  |
+-------------+-----------+--------------+-------+
|           1 |         1 |            1 | 25.30 |
|           2 |         1 |            1 | 25.30 |
|           3 |         1 |            2 |  5.30 |
|           4 |         1 |            2 |  2.30 |
|           5 |         2 |            1 |  1.30 |
|           6 |         2 |            1 |  6.00 |
|           7 |         2 |            2 | 50.00 |
|           8 |         3 |            1 |  3.50 |
|           9 |         3 |            1 |  2.50 |
|          10 |         3 |            2 |  1.30 |
+-------------+-----------+--------------+-------+

]SELECT fkEquipID,
SUM(IF(fkInstTypeID=1, rate, 0)) as rate1,
SUM(IF(fkInstTypeID=2, rate, 0)) as rate2
FROM feeschedule f
GROUP BY fkEquipID;

+-----------+-------+-------+
| fkEquipID | rate1 | rate2 |
+-----------+-------+-------+
|         1 | 50.60 |  7.60 |
|         2 |  7.30 | 50.00 |
|         3 |  6.00 |  1.30 |
+-----------+-------+-------+

[mikosiko]

here are several examples of crosstabs queries (pivot) that should help you

 

http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#78

[arenaninja]

Well I managed to solve it. There are a few subtleties in the solution I'm posting my solution in hopes that someone will one day use it [or if you can help me make it shorter ]. Here's the table, feeschedule:

 feeschedule | CREATE TABLE `feeschedule` (
  `pkRateID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fkEquipID` int(10) unsigned NOT NULL,
  `fkInstTypeID` int(10) unsigned NOT NULL,
  `rate` decimal(6,2) NOT NULL,
  `dateOfEffect` date NOT NULL,
  `dateOfUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fkUserID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pkFeeSchdID`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1

Here's my query (Note that there are 3 IF statements which are exactly the same -- these are automated with PHP):

SELECT equip.name_short AS `Abbrev.`,equip.name AS `Full Name`, 
IF(CHAR_LENGTH(GROUP_CONCAT(IF(feeschedule.fkInstTypeID=1,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)))>0, 
GROUP_CONCAT(IF(feeschedule.fkInstTypeID=1,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID,NULL)), CONCAT_WS(',',equip.equipid,'1')) AS `Rate1`, 
IF(CHAR_LENGTH(GROUP_CONCAT(IF(feeschedule.fkInstTypeID=2,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)))>0, 
GROUP_CONCAT(IF(feeschedule.fkInstTypeID=2,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID,NULL)), CONCAT_WS(',',equip.equipid,'2')) AS `Rate2`, 
IF(CHAR_LENGTH(GROUP_CONCAT(IF(feeschedule.fkInstTypeID=3,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)))>0, 
GROUP_CONCAT(IF(feeschedule.fkInstTypeID=3,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)), CONCAT_WS(',',equip.equipid,'3')) AS `Rate3`
FROM feeschedule RIGHT JOIN equip ON (equip.equipid=feeschedule.fkEquipID)
WHERE dateOfEffect<=:date GROUP BY equip.name

 

Now to break down the IF statement:

IF(CHAR_LENGTH(GROUP_CONCAT(IF(feeschedule.fkInstTypeID=1,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)))>0, 
GROUP_CONCAT(IF(feeschedule.fkInstTypeID=1,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)), CONCAT_WS(',',equip.equipid,'1')) AS `Rate1`

The basic IF() function in MySQL is IF(condition,this is done if true,this is done if false). The GROUP_CONCAT needs the GROUP BY clause to Pivot the results Note that it has a nested IF() statement, if a `rate` isn't defined, the length of this statement is zero.

 

Thus, what happens is the following: if there is a `rate` for this instType and instrument, the crosstab will display the value of the rate field and concatenate it with the rate's PK separated by the character 'i'.

If a `rate` doesn't exist for this instType and instrument, then the crosstab will return the instrument's PK and the FK for instType (so that I can make a link with a $_GET string to create one), separated by a comma.

The dateOfEffect argument is there because I allow rates to be set for periods of time -- the rate in June may not be the same as that for July, and I need the reports to be backward compatible so this flag is necessary. Note that the RIGHT JOIN ensures that we get a row for an element from table `equip` even if no `rate` is defined for it.

 

Apologies for the convoluted code, if anyone ever needs it I'm sure you can do a minimalist approach and work from there (it's why I supplied the CREATE TABLE code for this table). The truth is there's probably a much simpler way of doing this by using temporary tables. However, the advantage I have using this is that for the most part I can just loop through the query's result with PHP and translate it directly into a table format in HTML.