PostgreSQL Query with dynamic columns and counts from join

Question

I'm probably overthinking this, because I'm not really sure where to start... But here goes:

I have the following

Tables

students

assessments

students_assessments

Expected output

First query for "Attempts"

student_id | Assessment 1 | Assessment 2 | Assessment 3 | Assessment 4
1            3              1              2              0
2            1              0              0              0
3            2              1              1              0
4            5              3              3              0
5            1              5              0              0
6            2              1              2              0

Second query for "passed"

student_id | Assessment 1 | Assessment 2 | Assessment 3 | Assessment 4
1            t              t              f              f
2            t              t              f              f
3            t              t              f              f
4            t              t              t              f
5            t              t              f              f
6            t              t              t              f

The part that is tripping me up is not doing a join for EVERY assessment, and not even defining the columns manually but rather "generating" each column for assessments that exist.

I feel like it's simple, and I am just too overworked to figure it out right now :) thank you in advance for your help, and here's a SQL Fiddle of data as an example

Answer 1

Simple query for "Attempts",

select student_id,sum(case when assessment_id=1 then 1 else 0 end) as "Assessment 1",
sum(case when assessment_id=2 then 1 else 0 end) as "Assessment 2",
sum(case when assessment_id=3 then 1 else 0 end) as "Assessment 3",
sum(case when assessment_id=4 then 1 else 0 end) as "Assessment 4",
sum(case when assessment_id=5 then 1 else 0 end) as "Assessment 5",
sum(case when assessment_id=6 then 1 else 0 end) as "Assessment 6"
from assessments_students
group by student_id
order by student_id

In crosstab() function also, need to define columns name explicitly like "Assessment 1","Assessment 2" and so on.

Or write custom function for creating dynamic query, and execute using EXECUTE statement.

DROP FUNCTION get_Attempts() ;

CREATE OR REPLACE FUNCTION get_Attempts() RETURNS text AS
$BODY$
DECLARE
        r1 record;
        str_query text := '';
 BEGIN
    str_query :='select student_id,';
    FOR r1 IN SELECT "_id" , "name" FROM Assessments
    LOOP
      str_query:= str_query || 
                  'sum(case when assessment_id=' || r1."_id" || ' then 1 else 0 end) as  "' || r1.name ||'",' ;
    END LOOP;
    str_query:=trim( trailing  ',' from str_query); -- remove last semicolon
    str_query:= str_query || ' from assessments_students group by student_id order by student_id';
    return str_query;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM get_Attempts();

---

Second query for "passed"

select student_id,
max(case when assessment_id=1 and passed='t' then 't' else 'f' end) as  "Assessment 1",
max(case when assessment_id=2 and passed='t' then 't' else 'f' end) as  "Assessment 2",
max(case when assessment_id=3 and passed='t' then 't' else 'f' end) as  "Assessment 3",
max(case when assessment_id=4 and passed='t' then 't' else 'f' end) as  "Assessment 4",
max(case when assessment_id=5 and passed='t' then 't' else 'f' end) as  "Assessment 5",
max(case when assessment_id=6 and passed='t' then 't' else 'f' end) as  "Assessment 6" 
from assessments_students 
group by student_id 
order by student_id

and it's function look likes,

DROP FUNCTION get_passed() ;

CREATE OR REPLACE FUNCTION get_passed() RETURNS text AS
$BODY$
DECLARE
        r1 record;
        str_query text := '';
 BEGIN
    str_query :='select student_id,';
    FOR r1 IN SELECT "_id" , "name" FROM Assessments
    LOOP
      str_query:= str_query || 
                  'max(case when assessment_id=' || r1."_id" || ' and passed=''t'' then ''t'' else ''f'' end) as  "' || r1.name ||'",' ;
    END LOOP;
    str_query:=trim( trailing  ',' from str_query); -- remove last semicolon
    str_query:= str_query || ' from assessments_students group by student_id order by student_id';

    return str_query;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM get_passed();