Home Combine multiple different rows to make single row by converting rows to column attributes

Questions

Combine multiple different rows to make single row by converting rows to column attributes

September 10, 2022

I am joining 3 tables to get the desire result, but for one user I am getting 4 rows(only two column attributes are changed, all other attributes are same for one particular user).

My required output
I want to get single row which contains different rows attributes as separate columns instead of different attributes in 4 different rows.

Here is my current Query

SELECT
CONCAT(
    'https://example.com/?search=',
    comment_content
) AS link,
wp_comments.user_id,
user_email,
user_login,
user_nicename,
comment_date as 'Interview Date',
display_name,
user_registered,
meta_key,
meta_value

Thanks in advance

>Solution :

You need to GROUP BY the column that are always the same.

The rest is a aggregation function with a condothion

It is called pivot or oivting

SELECT
CONCAT(
    'https://example.com/?search=',
    comment_content
) AS link,
wp_comments.user_id,
user_email,
user_login,
user_nicename,
comment_date as 'Interview Date',
display_name,
user_registered,
MAX(CASE WHEN meta_key = 'first_name' THEN meta_value END) as 'first_name', 
MAX(CASE WHEN meta_key = 'last_name' THEN meta_value END) as 'last_name', 
MAX(CASE WHEN meta_key = 'user_birthday' THEN meta_value END) as 'user_brthday' 
MAX(CASE WHEN meta_key = 'user_sex' THEN meta_value END) as 'user_sex' 
FROM ...
WHERE ....
GROUP BY link,wp_comments.user_id,
user_email,
user_login,
user_nicename,
`Interview Date`,
display_name,
user_registered