Using the crosstab() function while appending to the column names & using boolean logic

Question

I'm using Postgresl 9.2

I need a crosstab table created from this:

select id, imp from sg_imp_id (There are A LOT more rows than this)

  id   |  imp  |
-------+-------+
  1    |  111  |
  2    |  111  | 
  2    |  121  | 
  2    |  122  |
  3    |  131  |
  4    |  154  |
  ....    ....

Like this:

    id   | x111 | x121 | x122 | x131 | x154 |
---------+------+------+------+------+------+
    1    |   1  |   0  |  0   |  0   |  0   |
    2    |   1  |   1  |  1   |  0   |  0   |
    3    |   0  |   0  |  0   |  1   |  0   |
    4    |   0  |   0  |  0   |  0   |  1   |

With a column for every imp row and whenever an id has that imp number, to place a 1. If it doesn't have that imp number then a 0 should be in that spot. I have very limited knowledge of the crosstab() function. There are currently very many different rows of "x111,x112,x113" values so using the case clause won't really be probable.

Answer 1

Not sure about crosstab() function, but you can always pivot manually:

select
    id,
    max(case when imp = 111 then 1 else 0 end) as x111,
    max(case when imp = 121 then 1 else 0 end) as x121,
    max(case when imp = 122 then 1 else 0 end) as x122,
    max(case when imp = 131 then 1 else 0 end) as x131,
    max(case when imp = 154 then 1 else 0 end) as x154
from Table1
group by id

sql fiddle demo