CODEWITHSUNDEEP
mysql
bgunning
bgunning

Reputation: 27

MySQL CASE - How to handle multiple results for a single CASE statement

I hope I am using the correct terminology in my title, but I have this (simplified) SQL statement:

SELECT name,  
MAX(CASE WHEN org_assigned_role= "CFO" THEN username END) "CFO",
MAX(CASE WHEN org_assigned_role= "CIO" THEN username END) "CIO"
FROM `wp_hubspot` , `wp_hubspot_companies` where company_id=comp_id 
GROUP BY name
ORDER BY name

The problem is that I have 2 CFOs in my table and I want both of their names in the single field/column "CFO". Is that possible?

Thank you

Upvotes: 0

Views: 256

Answers (1)

ysth
ysth

Reputation: 98398

It sounds like you want:

select name,
    GROUP_CONCAT(DISTINCT CASE WHEN org_assigned_role="CFO" THEN username END ORDER BY username SEPARATOR ", ") "CFO",
    MAX(CASE WHEN org_assigned_role= "CIO" THEN username END) "CIO"
    FROM `wp_hubspot` , `wp_hubspot_companies` where company_id=comp_id 
    GROUP BY name

If what you mean is you want the two on separate rows, there are multiple results like that you could mean and you will need to provide sample data and expected output.

Note that the result of GROUP_CONCAT is truncated at @@group_concat_max_len characters (defaults to 1024 in mysql and in older versions of mariadb).

Upvotes: 1

Related Questions