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

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

CodePudding user response：

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