Mysql-PHP，基于变量字段的旋转/交叉制表符

Question

Hello Everybody and thank you in advance for your help!

I am trying to create a crosstab type query result based on a returned query. The first query :

SELECT DISTINCT
dc.`name`,
Count(pd.dispositionCodeId) AS NumberOfDispos
FROM
pd

 JOIN ph ON (pd.packetHeaderId = ph.packetHeaderId)
 JOIN sc ON (sc.packetHeaderId = ph.packetHeaderId)
 JOIN dc ON pd.dispositionCodeId = dc.dpcodeId
WHERE (`ph`.`customerId` = 60)
GROUP BY
dc.`name`

resulting in:

Name NumberofDispos
B      400
NO     245
S      134
V      98

-- The name that I'm returning are variable depending on the customerId, and also on additional where clauses that appear on other similar queries.

Is there a way to pivot the results of the name field as column headings in a second table, which a second query will count the occurances of that particular name showing up in another matched field?

        B   NO  S   V 
PackA   250 120 61  22
PackB   100 23  62  37
PackC   50  102 11  39

Please keep in mind the column headings are variable based on the results fromt the first query.

As far as I can tell, I would need to make a php array and iterate through the sql results

Answer 1

This type of query is known as a pivot but unfortunately MySQL does not have a pivot function so you will want to replicate it using an aggregate function with a CASE statement.

If the values are known ahead of time, then you can hard-code the values into the following:

SELECT pd.dispositionCodeId,
  sum(case when dc.`name` = 'B' then 1 else 0 end) B,
  sum(case when dc.`name` = 'NO' then 1 else 0 end) NO,
  sum(case when dc.`name` = 'S' then 1 else 0 end) S,
  sum(case when dc.`name` = 'V' then 1 else 0 end) V
FROM pd
JOIN ph 
  ON pd.packetHeaderId = ph.packetHeaderId
JOIN sc 
  ON sc.packetHeaderId = ph.packetHeaderId
JOIN dc 
  ON pd.dispositionCodeId = dc.dpcodeId
WHERE (`ph`.`customerId` = 60)
GROUP BY pd.dispositionCodeId;

But if the values or number of names are unknown then you can use a prepared statement which creates a dyanmic sql statement to execute. The code will look like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when dc.`name` = ''',
      name,
      ''' then 1 else 0 end) AS ',
      name
    )
  ) INTO @sql
FROM dc;

SET @sql = CONCAT('SELECT pd.dispositionCodeId, ', @sql, ' 
                   FROM pd
                   JOIN ph 
                     ON pd.packetHeaderId = ph.packetHeaderId
                   JOIN sc 
                     ON sc.packetHeaderId = ph.packetHeaderId
                   JOIN dc 
                     ON pd.dispositionCodeId = dc.dpcodeId 
                   WHERE `ph`.`customerId` = 60
                   GROUP BY pd.dispositionCodeId');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;