postgres - transpose columns to rows

If you wanted to do the opposite (which is really more common) you could use hte tablefunc contrib module. But this way, you can simply use UNION ALL and query each column separately:

SELECT id, 'class-1' AS class-type,
       class-1-male AS male, class-1-female AS female
FROM your_table
UNION ALL
SELECT id, 'class-2' AS class-type,
       class-2-male AS male, class-2-female AS female
FROM your_table
UNION ALL
SELECT id, 'class-3' AS class-type,
       class-3-male AS male, class-3-female AS female
FROM your_table

If you have too many rows on this table, this may not be the best option, because it reads the entire dataset for each query, which means it read them all three times when it could read just once. Another option would be creating a function that returns the set:

CREATE OR REPLACE FUNCTION classes_transpose()
RETURNS TABLE(id int, class_type text, male int, female int)
LANGUAGE PLPGSQL
STABLE
AS $$
DECLARE
    v RECORD;
BEGIN
    FOR v IN SELECT * FROM your_table LOOP
        id := v.id;
        class_type := 'class-1';
        male := v."class-1-male";
        female := v."class-1-female";
        RETURN NEXT;
        class_type := 'class-2';
        male := v."class-2-male";
        female := v."class-2-female";
        RETURN NEXT;
        class_type := 'class-3';
        male := v."class-3-male";
        female := v."class-3-female";
        RETURN NEXT;
    END LOOP;
END;
$$;

Then:

SELECT * FROM classes_transpose();