Row to Column in MySQL

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:

idname
1Kesavan
2Madhavan
user_idattribute_nameattribute_value
1GENDERM
1PREMIUM_USERN
1SUBSCRIBED_TO_NEWSLETTERY
2GENDERM
2PREMIUM_USERY

To get a result like this:

idnameGENDERPREMIUM_USERSUBSCRIBED_TO_NEWSLETTER
1KesavanMNY
2MadhavanMYNULL

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

Leave a Reply

Your email address will not be published. Required fields are marked *