查询结果做特殊行列转换
Example
id | type
1 | 2
1 | 3
1 | 2
2 | 1
2 | 2
2 | 3
Output
id || type1 || type2 || type3 || type4
1 || 0 || 1 || 1 || 1
2 || 1 || 1 || 1 || 0
Resolution
select t.id, sum(t.a), sum(t.b), sum(t.c), sum(t.d)
from (select t.id,
t.type,
case
when t.type = 1 then
1
else
0
end as a,
case
when t.type = 2 then
1
else
0
end as b,
case
when t.type = 3 then
1
else
0
end as c,
case
when t.type = 4 then
1
else
0
end as d
from t_test t
group by t.id, t.type) t
group by t.id