sql - Selecting values from a table where values from one column is divided into multiple columns -

i'm trying develop query in values of single column split 2 or more separate columns reflect whether or not particular id has each of different values.

for example, have table this:

------------ | id | val | |----|-----| | 1  |   | | 1  |  b  | | 2  |   | | 3  |   | | 4  |  b  | | 5  |   | ------------ 

the query produce table looks this:

---------------------- | id | val_1 | val_2 | |----|-------|-------| | 1  |     |  b    | | 2  |     |       | | 3  |     |       | | 4  |       |  b    | | 5  |     |       | ---------------------- 

specifically, query show id's have missing values (i.e. id "1" knocked off table).

so far have tried using self inner join, have not been able find select clauses produce this.

any suggestions?

thanks!

update : filtering out columns have val_1 , val_2

select *  ( select id, max(case when val='a' 'a' end) val_1,             max(case when val='b' 'b' end) val_2 table1 group id )a val_1 null or val_2 null;