Say you have two tables like these:
CREATE TABLE user (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
) ENGINE = 'InnoDB';
CREATE TABLE user_attributes(
user_id INT(11) UNSIGNED NOT NULL,
attribute_name VARCHAR(255) NOT NULL,
attribute_value VARCHAR(255) NOT NULL,
UNIQUE KEY (user_id, attribute_name)
) ENGINE = 'InnoDB';And you have these values:
| id | name |
| 1 | Kesavan |
| 2 | Madhavan |
| user_id | attribute_name | attribute_value |
| 1 | GENDER | M |
| 1 | PREMIUM_USER | N |
| 1 | SUBSCRIBED_TO_NEWSLETTER | Y |
| 2 | GENDER | M |
| 2 | PREMIUM_USER | Y |
To get a result like this:
| id | name | GENDER | PREMIUM_USER | SUBSCRIBED_TO_NEWSLETTER |
| 1 | Kesavan | M | N | Y |
| 2 | Madhavan | M | Y | NULL |
Use this query
SELECT
u.id,
ANY_VALUE(u.name) as name,
GROUP_CONCAT(IF (ua.attribute_name = 'GENDER', ua.attribute_value, NULL)) AS 'GENDER',
GROUP_CONCAT(IF(ua.attribute_name = 'PREMIUM_USER', ua.attribute_value, NULL)) AS 'PREMIUM_USER',
GROUP_CONCAT(IF(ua.attribute_name = 'SUBSCRIBED_TO_NEWSLETTER', ua.attribute_value, NULL)) AS 'SUBSCRIBED_TO_NEWSLETTER'
FROM user u
JOIN user_attributes ua ON (u.id = ua.user_id)
GROUP BY u.id;Dbfiddle link: https://www.db-fiddle.com/f/adNt6wy9fMoVudrbeoPtrU/3