Transpose key -> value table (rows) into columns-CodePudding

We have a metadata pair key value table where we store daily kpis on a similar format to:

 ------ --------- ------------- ------------  
| id   | key     | value       | last_update| 
 ------ --------- ------------- ------------  
|    1 | key1    | foo         | 2022-08-08 | 
|    2 | key2    | bar         | 2022-08-08 | 
|    3 | key4    | more        | 2022-08-08 | 
|    4 | key2    | galaxy      | 2022-08-07 | 
|    5 | key3    | foo         | 2022-08-06 | 
|    6 | key4    | other       | 2022-08-06 | 
 ------ --------- ------------- ------------

Only changed data from previous value gets saved so not all keys get created every day. New keys can appear any time.

In order to represent some output charts I am looking for query for MySQL that could transpose the values to a more "traditional" format of one per day for all the keys that exists that day.

 --------- ---------- --------- -------- ------------ 
| key1    | key2     | key3    | key4   | date       |
 --------- ---------- --------- -------- ------------ 
| foo     | bar      | NULL    | more   | 2022-08-08 |
| NULL    | galaxy   | NULL    | NULL   | 2022-08-07 |
| NULL    | NULL     | foo     | other  | 2022-08-06 |
 --------- ---------- --------- -------- ------------

I've tried different approaches to insert records based on a

select DISTINCT id from source table ORDER BY id

based on similar replies here in stackoverflow but without success.

CodePudding user response：

This is a pivot table given your example:

SELECT
 MAX(CASE `key` WHEN 'key1' THEN `value` END) AS `key1`,
 MAX(CASE `key` WHEN 'key2' THEN `value` END) AS `key2`,
 MAX(CASE `key` WHEN 'key3' THEN `value` END) AS `key3`,
 MAX(CASE `key` WHEN 'key4' THEN `value` END) AS `key4`,
 last_update AS date
FROM `source table`
GROUP BY date;

You do need to know the distinct values for key to format the query.

One must assume there's only one value for a given key on a given date.

CodePudding user response：

I use GROUP_CONCAT to solve this kind of problem unles you want only the first or last value of the group. Showing all associated values is important to not missing any information.

SELECT
 GROUP_CONCAT(CASE `key` WHEN 'key1' THEN `value` END) AS `key1`,
 GROUP_CONCAT(CASE `key` WHEN 'key2' THEN `value` END) AS `key2`,
 GROUP_CONCAT(CASE `key` WHEN 'key3' THEN `value` END) AS `key3`,
 GROUP_CONCAT(CASE `key` WHEN 'key4' THEN `value` END) AS `key4`,
 last_update AS date
FROM `source table`
GROUP BY date;