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)?


1 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
  • 1
    Thanks very much - "pivot" vs "dynamic pivot" is the clue I was looking for.
    – Jack Deeth
    Commented Jun 13, 2021 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.