sql - Select rows with same id but different result in another column -

sql: have table this:

+------+------+ |id    |result| +------+------+ |1     |a     | +------+------+ |2     |a     | +------+------+ |3     |a     | +------+------+ |1     |b     | +------+------+ |2     |b     | +------+------+ 

the output should like:

output:

+------+-------+-------+ |id    |result1|result2| +------+-------+-------+ |1     |a      |b      | +------+-------+-------+ |2     |a      |b      | +------+-------+-------+ |3     |a      |       | +------+-------+-------+ 

how can this?

select     id,     max((case result when 'a' 'a' else null end)) result1,     max((case result when 'b' 'b' else null end)) result2,      table1 group id 

results

+------+-------+-------+ |id    |result1|result2| +------+-------+-------+ |1     |a      |b      | |2     |a      |b      | |3     |a      |null   | +------+-------+-------+ 

run live demo on sql fiddle: (http://sqlfiddle.com/#!9/e1081/2)