0

I have data like the following:

create temp table codes (id integer, code text);
insert into codes (id, code) values
  (1, 'code_1'),
  (1, 'code_2'),
  (2, 'code_2'),
  (3, 'code_1');

In order to facilitate a select that joins on the id field, I'd like to pivot this table using crosstab, so that each id value is unique. So far I have this design, which works for a single code value:

select *
from crosstab($$
  select id, code, case when code = 'code_1' then true else false end
  from codes
  order by 1,2
$$) as codes(id int, is_code_1 boolean);

Correct output:

 id | is_code_1
----+-----------
  1 | t
  2 | f
  3 | t
 (3 rows)

I'd like to be able to extend this to multiple code values so that I end up with output like this:

 id | is_code_1 | is_code_2
----+-----------+-----------
  1 | t         | t
  2 | f         | t
  3 | t         | f

Not sure this is possible, but would be very happy to learn otherwise.

Edit: This gets me really close to the desired outcome, may provide it as an answer because I can use it to solve me problem. Thanks @Daniel Vérité for the link to 2 parameter crosstab.

select *
from crosstab(
  $$
  select id, code, case when code is not null then true else false end
  from codes
  order by 1,2
  $$,
  $$values ('code_1'), ('code_2')$$
) as codes(id int, code_1 boolean, code_2 boolean);

Produces:

 id | code_1 | code_2
----+--------+--------
  1 | t      | t
  2 |        | t
  3 | t      |

So nulls instead of boolean false values, but I can work with that.

sage88
  • 4,104
  • 4
  • 31
  • 41

1 Answers1

0

Not sure if I have understood you correctly, but this code may answer your question: 

select *
  from crosstab($$
  select id, code, case when code = 'code_1' then true  
                          when code = 'code_2' then true else false  end
  from codes
  order by 1,2
$$) as codes(id int, is_code_1 boolean, is_code_2 boolean);
Ms workaholic
  • 373
  • 2
  • 8
  • 21