There is 3 tables

Table User

Table Default_Properties

Table Custom_Properties

By default, every user will have default properties, unless overwritten with custom one. Thus, expected result as listed below:

Expected Result

I tried to pivot it by using "case when" but have no luck to get the expected result. Highly appreciate for any query help/suggestion provided. Thank you!

CodePudding user response：

SELECT u.user_id,
  MAX(CASE d.type WHEN 'type_a' THEN COALESCE(c.value, d.value) END) AS type_a,
  MAX(CASE d.type WHEN 'type_b' THEN COALESCE(c.value, d.value) END) AS type_b,
  MAX(CASE d.type WHEN 'type_c' THEN COALESCE(c.value, d.value) END) AS type_c,
  MAX(CASE d.type WHEN 'type_d' THEN COALESCE(c.value, d.value) END) AS type_d,
  MAX(CASE d.type WHEN 'type_e' THEN COALESCE(c.value, d.value) END) AS type_e
FROM user AS u
CROSS JOIN Default_Properties AS d 
LEFT OUTER JOIN Custom_Properties AS c ON u.user_id=c.fk_user_id AND c.type=d.type
GROUP BY u.user_id
ORDER BY u.user_id;

Output, tested on MySQL 8.0.29:

 --------- -------- -------- -------- -------- -------- 
| user_id | type_a | type_b | type_c | type_d | type_e |
 --------- -------- -------- -------- -------- -------- 
|    1001 | val_a  | val_b  | val_c  | val_d  | val_e  |
|    1002 | val_a  | banana | val_c  | val_d  | val_e  |
|    1003 | val_a  | val_b  | val_c  | val_d  | val_e  |
|    1004 | val_a  | val_b  | val_c  | val_d  | val_e  |
|    1005 | val_a  | val_b  | val_c  | durian | val_e  |
 --------- -------- -------- -------- -------- -------- 

It's not possible in SQL to do "dynamic columns."

Columns must be fixed at the time the query is parsed, which is before it has read any of the data in the table. So there's no way a query can expand the columns as it reads data, beyond those you name explicitly in the select-list when you write the query.

By analogy, this would be like calling a Java function whose name is the return value of the function.

So to do an effective pivot table, if the properties you want to make columns for may change, then you must do the query as two steps: first know the set of properties you want to make columns for. Something like the following would give you the list of properties:

SELECT DISTINCT type FROM Default_Properties
UNION
SELECT DISTINCT type FROM Custom_Properties;

Then use the result of that query to build your pivot table query. (I.e. a loop of code, appending expressions to the select-list of a query as strings.)