Say I have this data set
user | group --------+------- [email protected] | A [email protected] | B [email protected] | A [email protected] | B [email protected] | A [email protected] | B [email protected] | C
I want to convert this into a table like this:
user | IN_A | IN_B | IN_C --------+-------+-------+------- [email protected] | TRUE | TRUE | FALSE [email protected] | TRUE | FALSE | FALSE [email protected] | FALSE | TRUE | FALSE [email protected] | TRUE | TRUE | TRUE
I've got:
SELECT user, IF(LOCATE('A', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_A, IF(LOCATE('B', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_B, IF(LOCATE('C', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_C FROM users GROUP BY user
What I'm wondering is if there is a better way to known if an aggregated field contains a value, or if this is the only way?
The right way to do it:
SELECT
user,
IF(SUM(group = 'A'), TRUE, FALSE) AS IN_A,
IF(SUM(group = 'B'), TRUE, FALSE) AS IN_B,
IF(SUM(group = 'C'), TRUE, FALSE) AS IN_C
FROM users
GROUP BY user
I guess this is the only way i.e., GROUP_CONCAT
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With