CODEWITHSUNDEEP
mysqlsqldatabase
iClustering
iClustering

Reputation: 11

Matrix in MySql when I have 3 columns

I have one problem with making matrix in MySql. I have data like this:

a | 1 | +

a | 3 | +

a | 4 | -

b | 3 | -

b | 1 | +

b | 4 | +

it is possible to make matrix like this:

_|1 3 4

a|+ + -

b|+ - +

????

Upvotes: 1

Views: 114

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

This is a form of pivot, but with a nuance, because the missing value needs to be replaced by -. In MySQL you can do this with conditional aggregation:

select col1,
       max(case when col2 = 1 then col3 else '-' end),
       max(case when col2 = 3 then col3 else '-' end),
       max(case when col2 = 4 then col3 else '-' end)
from table t
group by col1;

Upvotes: 1

Related Questions