Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (student_id int, site text, start_date date, end_date date, primary_or_secondary text);
INSERT INTO tbl VALUES
  (1, 'A', '2019-01-01', '2019-02-28', 'Primary')
, (1, 'B', '2019-02-01', '2019-06-30', 'Secondary')
, (1, 'C', '2019-03-01', '2019-06-30', 'Primary')
, (1, 'D', '2020-03-01', '2020-06-30', 'Primary')  -- extended test case
, (1, 'E', '2020-03-01', '2020-04-01', 'Secondary')
, (1, 'F', '2020-04-04', '2020-05-01', 'Secondary')
, (1, 'G', '2020-05-02', '2020-06-30', 'Secondary')
, (1, 'H', '2020-07-01', '2020-07-31', 'Primary')
, (1, 'I', '2020-08-01', '2020-08-31', 'Secondary')

, (3, 'C', '2019-03-01', '2019-06-30', 'Primary')  -- more users
, (2, 'A', '2019-01-01', '2019-02-28', 'Primary')
, (3, 'B', '2019-02-01', '2019-06-30', 'Secondary')
, (2, 'C', '2019-03-01', '2019-06-30', 'Primary')
, (3, 'A', '2019-01-01', '2019-02-28', 'Primary')
, (2, 'B', '2019-02-01', '2019-06-30', 'Secondary')
, (4, 'B', '2019-02-01', '2019-06-30', 'Secondary')
;
16 rows affected

CREATE OR REPLACE FUNCTION f_student_xtab(_student_ids int[])
  RETURNS TABLE (
      student_id int
    , "primary"  text
    , secondary  text
    , start_date date
    , end_date   date) AS
$func$
DECLARE
   r record;
BEGIN
   student_id := -1; -- init with impossible value

   FOR r IN
      SELECT t.student_id, t.site, t.primary_or_secondary = 'Primary' AS prim, l.range_end, l.date
      FROM   tbl t
      CROSS  JOIN LATERAL (
         VALUES (false, t.start_date)
              , (true , t.end_date)
         ) AS l(range_end, date)
      WHERE  t.student_id = ANY (_student_ids)
      ORDER  BY t.student_id, l.date, range_end -- start of range first
   LOOP
      IF r.student_id <> student_id THEN
         student_id := r.student_id;
         IF r.prim THEN "primary" := r.site;
                   ELSE secondary := r.site;
         END IF;
         start_date := r.date;
      ELSIF r.range_end THEN
         IF r.date < start_date THEN
            -- range already reported
            IF r.prim THEN "primary" := NULL;
                      ELSE secondary := NULL;
            END IF;
            start_date := NULL;
         ELSE
            end_date := r.date;
            RETURN NEXT;
            IF r.prim THEN
               "primary" := NULL;
               IF secondary IS NULL THEN start_date := NULL;
                                    ELSE start_date := r.date + 1;
               END IF;
            ELSE
               secondary := NULL;
               IF "primary" IS NULL THEN start_date := NULL;
                                    ELSE start_date := r.date + 1;
               END IF;
            END IF;
            end_date := NULL;
         END IF;
      ELSE  -- range starts
         IF r.date > start_date THEN
            -- range already running
            end_date := r.date - 1;
            RETURN NEXT;
         END IF;
         start_date := r.date;
         end_date := NULL;
         IF r.prim THEN "primary" := r.site;
                   ELSE secondary := r.site;
         END IF;
      END IF;
   END LOOP;
END
$func$  LANGUAGE plpgsql;
SELECT * FROM f_student_xtab('{1,2,3}');


student_id
primary
secondary
start_date
end_date


1
A
null
2019-01-01
2019-01-31

1
A
B
2019-02-01
2019-02-28

1
C
B
2019-03-01
2019-06-30

1
D
E
2020-03-01
2020-04-01

1
D
null
2020-04-02
2020-04-03

1
D
F
2020-04-04
2020-05-01

1
D
G
2020-05-02
2020-06-30

1
H
null
2020-07-01
2020-07-31

1
null
I
2020-08-01
2020-08-31

2
A
null
2019-01-01
2019-01-31

2
A
B
2019-02-01
2019-02-28

2
C
B
2019-03-01
2019-06-30

3
A
null
2019-01-01
2019-01-31

3
A
B
2019-02-01
2019-02-28

3
C
B
2019-03-01
2019-06-30

student_id	primary	secondary	start_date	end_date
1	A	null	2019-01-01	2019-01-31
1	A	B	2019-02-01	2019-02-28
1	C	B	2019-03-01	2019-06-30
1	D	E	2020-03-01	2020-04-01
1	D	null	2020-04-02	2020-04-03
1	D	F	2020-04-04	2020-05-01
1	D	G	2020-05-02	2020-06-30
1	H	null	2020-07-01	2020-07-31
1	null	I	2020-08-01	2020-08-31
2	A	null	2019-01-01	2019-01-31
2	A	B	2019-02-01	2019-02-28
2	C	B	2019-03-01	2019-06-30
3	A	null	2019-01-01	2019-01-31
3	A	B	2019-02-01	2019-02-28
3	C	B	2019-03-01	2019-06-30