Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table public.test123 (
    person      VARCHAR(20),
    outcome     VARCHAR(20));
    
insert into public.test123(person, outcome)
    VALUES
        ('Peter', 'positive'),
        ('Peter', 'positive'),
        ('Peter', 'positive'),
        ('Eric', 'positive'),
        ('Eric', 'positive'),
        ('Eric', 'negative');
        
SELECT * FROM test123

CREATE TABLE



INSERT 0 6




person
outcome


Peter
positive

Peter
positive

Peter
positive

Eric
positive

Eric
positive

Eric
negative



SELECT 6


SELECT
    *
FROM unnest(ARRAY['positive', 'negative']) as x(outcome)


outcome


positive

negative



SELECT 2


SELECT
    *
FROM unnest(ARRAY['positive', 'negative']) as x(outcome),
(
    SELECT DISTINCT
        person
    FROM test123
) s


outcome
person


positive
Eric

positive
Peter

negative
Eric

negative
Peter



SELECT 4


SELECT
    *
FROM (
    SELECT
        *
    FROM unnest(ARRAY['positive', 'negative']) as x(outcome),
    (
        SELECT DISTINCT
            person
        FROM test123
    ) s
) s
LEFT JOIN test123 t ON t.person = s.person AND t.outcome = s.outcome


outcome
person
person
outcome


positive
Peter
Peter
positive

positive
Peter
Peter
positive

positive
Peter
Peter
positive

positive
Eric
Eric
positive

positive
Eric
Eric
positive

negative
Eric
Eric
negative

negative
Peter
null
null



SELECT 7


SELECT
    s.person,
    s.outcome,
    (t.outcome IS NOT NULL)::int
FROM (
    SELECT
        *
    FROM unnest(ARRAY['positive', 'negative']) as x(outcome),
    (
        SELECT DISTINCT
            person
        FROM test123
    ) s
) s
LEFT JOIN test123 t ON t.person = s.person AND t.outcome = s.outcome


person
outcome
int4


Peter
positive
1

Peter
positive
1

Peter
positive
1

Eric
positive
1

Eric
positive
1

Eric
negative
1

Peter
negative
0



SELECT 7


SELECT
    s.person,
    s.outcome,
    SUM((t.outcome IS NOT NULL)::int) as cnt
FROM (
    SELECT
        *
    FROM unnest(ARRAY['positive', 'negative']) as x(outcome),
    (
        SELECT DISTINCT
            person
        FROM test123
    ) s
) s
LEFT JOIN test123 t ON t.person = s.person AND t.outcome = s.outcome
GROUP BY s.person, s.outcome


person
outcome
cnt


Peter
positive
3

Peter
negative
0

Eric
positive
2

Eric
negative
1



SELECT 4
person	outcome
Peter	positive
Peter	positive
Peter	positive
Eric	positive
Eric	positive
Eric	negative