My schema looks like,
CREATE TABLE `Log` (
`id` int,
`changeData` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
The data looks like,
INSERT INTO `Log`
(`id`,`changeData`)
VALUES
(1,'{"value": 4680, "SalaryType": "Housing Allowance"} '),
(1,'{"value": 5000, "SalaryType": "Other"} '),
(2,'{"value": 10000, "SalaryType": "Housing Allowance"} '),
(2,'{"value": 5500, "SalaryType": "Other"} '),
(2,'{"value": 700, "SalaryType": "Transport"} ')
;
The plan is to show the salary type as column headers. The following query will work but this would mean hard coding every possible value for SalaryType in case statements, which could number in the tens and new ones are added everytime which would mean adding more case statements.
SELECT
`id`,
MAX(CASE
WHEN TRIM(BOTH '"' FROM (JSON_EXTRACT(changeData, '$.SalaryType'))) = 'Housing Allowance' THEN JSON_EXTRACT(changeData, '$.value')
ELSE 0
END) AS 'Housing Allowance',
MAX(CASE
WHEN TRIM(BOTH '"' FROM (JSON_EXTRACT(changeData, '$.SalaryType'))) = 'Other' THEN JSON_EXTRACT(changeData, '$.value')
ELSE 0
END) AS 'Other',
MAX(CASE
WHEN TRIM(BOTH '"' FROM (JSON_EXTRACT(changeData, '$.SalaryType'))) = 'Transport' THEN JSON_EXTRACT(changeData, '$.value')
ELSE 0
END) AS 'Transport'
FROM
`Log`
GROUP BY `id`;
Output for the query is as below, which is correct,
id Housing Allowance Other Transport
1 4680 5000 0
2 10000 5500 700
Is there a way to do this without hard coding each value for SalaryType
in case statements?
dbfiddle
question from:
https://stackoverflow.com/questions/65924552/extract-and-display-field-values-as-headers-dynamically-in-mysql-8 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…