Pivoting a two-column table to show if a permutation exists

Question

Struggling with the terminology here...

I have a table of elements which may or may not be present in a group:

elem | grp
----------
1    | A
1    | B
2    | A
3    | C

I'd like to turn this into a table showing true/false for each permutation:

elem | A     | B     | C
----------------------------
1    | true  | true  | false
2    | true  | false | false
3    | false | false | true

There's an arbitrary number of elements and groups - I can't hardcode a list of groups into the query.

What is the name of what I'm trying to do here? And is there a canonical solution (which can be implemented within a single SELECT statement - subqueries and CTEs are fine - in Postgres9.6 to drive a SAP BusinessObjects report)?

Answer 1

This gets you everything except the pivot, so you're left with a pure dynamic pivot problem. As the other commenter says, it doesn't look like you'll be able to do so in a single select.

CREATE TEMP TABLE input_table (element INT, grp VARCHAR(10));

INSERT INTO input_table (element, grp)
VALUES (1, 'A'),
       (1, 'B'),
       (2, 'A'),
       (3, 'C');

;WITH grps AS (
 SELECT DISTINCT grp FROM input_table
),
elems AS (
  SELECT DISTINCT element FROM input_table
),
potentialCombos AS (
  SELECT e.element, g.grp
  FROM elems e
  CROSS JOIN grps g
)
SELECT pc.element, pc.grp, CASE WHEN i.element IS NULL THEN 0 ELSE 1 END present
FROM potentialCombos pc
LEFT JOIN input_table i ON i.element = pc.element AND i.grp = pc.grp