CODEWITHSUNDEEP
mysqlsqldatabase
Franziska
Franziska

Reputation: 115

MySQL: Multiple rows to multiple fields?

I have this table:

userid | property
1 | propA
1 | propC
2 | propA
2 | propB

And need this output for an csv export/import

userid;propA;propB;probC
1;yes;no;yes
2;yes;yes;no

Is that possible without any script language like php? It would be totally okay for me if "yes" and "no" are just "1" and "0" (e.g.) but it is important that I need just one row for each user and one field for each property.

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use conditional aggregation. However, your format is not CSV (hint: the "C" means comma).

To get that format, you can do:

select t.userid,
       max(case when t.property = 'propA' then 'Yes' else 'No' end) as PropA,
       max(case when t.property = 'propB' then 'Yes' else 'No' end) as PropB,
       max(case when t.property = 'propC' then 'Yes' else 'No' end) as PropC
from table t
group by t.userid;

This happens to work because "Yes" is later in the alphabet than "No". Personally, I would just use numbers, 0 and 1:

select t.userid,
       max(t.property = 'propA') as PropA,
       max(t.property = 'propB') as PropB,
       max(t.property = 'propC') as PropC
from table t
group by t.userid;

Upvotes: 1

Related Questions