MS Access Transform Query to MySQL Query

Question

I am moving a MS Access database to MySQL and I am running into a problem with a few of the queries.

I am about 80% finished with transferring the queries, but this particular one is giving me trouble.

TRANSFORM Nz(Max(IIf([charge_description_code] In ('AHS','AHW','AHL','RES','SAT'),"Y","N")), "N") AS Data
SELECT shipment_details.tracking_number, shipment_details.invoice_number
FROM shipment_details
WHERE tracking_number is not null
and charge_category_detail_code not in ('RADJ')
GROUP BY shipment_details.tracking_number, shipment_details.invoice_number
PIVOT shipment_details.charge_description_code In (SAT,AHW,AHS,AHL,RES);

I expect the results to be displayed like:

tracking_number invoice_number  SAT AHW AHS AHL RES
1Z2XXXXXXXXX625816  1329102975  N   N   N   N   N
1Z3YYYYYYYYY610514  1329109647  N   N   Y   N   N
1Z3ZZZZZZZZZ142605  1329109647  N   N   N   Y   Y

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Rene, Jul 08 '19 at 00:45

score 0 · Accepted Answer · answered Jul 08 '19 at 01:13

Consider conditional aggregation which would still work in MS Access (replace IF with IIF below). Usually, Access' crosstab query is for pivoted columns more than a handful unlike your current version of five. Also, your NZ is redundant as NULL resolves to N per the conditional logic.

SELECT s.tracking_number, 
       s.invoice_number,
       MAX(IF(s.[charge_description_code] = 'SAT', 'Y', 'N')) AS 'SAT',
       MAX(IF(s.[charge_description_code] = 'AHW', 'Y', 'N')) AS 'AHW',
       MAX(IF(s.[charge_description_code] = 'AHL', 'Y', 'N')) AS 'AHL',
       MAX(IF(s.[charge_description_code] = 'AHS', 'Y', 'N')) AS 'AHS',
       MAX(IF(s.[charge_description_code] = 'RES', 'Y', 'N')) AS 'SAT'

FROM shipment_details s
WHERE s.tracking_number IS NOT NULL
  AND s.charge_category_detail_code NOT IN ('RADJ')
  AND s.charge_description_code IN ('AHS','AHW','AHL','RES','SAT')

GROUP BY s.tracking_number, 
         s.invoice_number

Thank you very much for the response Parfait! I had to remove the brackets around [charge_description_code] and then the query ran, but it is a little off. I would like to return N for all fields (SAT, AHW, AHL, AHS, RES) if they do not meet the condition. Right now, the rows are being excluded because of the AND s.charge_description_code IN ('AHS','AHW','AHL','RES','SAT') condition. — Stephen, Jul 08 '19 at 01:29
Disregard previous comment -- I just removed AND s.charge_description_code IN ('AHS','AHW','AHL','RES','SAT') and it is what I need. Thank you again — Stephen, Jul 08 '19 at 02:10

score 0 · Answer 2 · answered Jul 08 '19 at 02:46

I think the transform returns rows where none of the values are set. This being the case, the appropriate transformation is more like this:

SELECT s.tracking_number, s.invoice_number,
       MAX(CASE WHEN s.charge_description_code = 'SAT' THEN 'Y' ELSE 'N' END) AS SAT,
       MAX(CASE WHEN s.charge_description_code = 'AHW' THEN 'Y' ELSE 'N' END) AS AHW,
       MAX(CASE WHEN s.charge_description_code = 'AHL' THEN 'Y' ELSE 'N' END) AS AHL,
       MAX(CASE WHEN s.charge_description_code = 'AHS' THEN 'Y' ELSE 'N' END) AS AHS,
       MAX(CASE WHEN s.charge_description_code = 'RES' THEN 'Y' ELSE 'N' END) AS RES
FROM shipment_details s
WHERE s.tracking_number IS NOT NULL AND
      s.charge_category_detail_code NOT IN ('RADJ')
GROUP BY s.tracking_number, s.invoice_number;

MS Access Transform Query to MySQL Query

2 Answers2