postgres -将列转置为行

Question

我需要在postgres中将列转置为行，以下是要求。任何帮助都是非常感谢的。

源表/数据：

id   class-1-male class-1-female class-2-male  class-2-female class-3-male  class-3-female 
1      1                1                11              7          0                9
2      11              31                6              7          40               92
3      15              31                8              37         30               91
4      11              13                50             17         10               19

我需要以下格式的数据：

id   class-type male female
1     class-1    1     1
2     class-1    11    31
3     class-1    15    31
4     class-1    11    13
1     class-2    11    7
2     class-2    6     7
3     class-2    8     37
4     class-2    50    17
1     class-3    0     9
2     class-3    40    92
3     class-3    30    91
4     class-3    10    19

Answer 1

如果你想做相反的事情(这实际上更常见)，你可以使用tablefunc contrib模块。但是通过这种方式，您可以简单地使用UNION ALL并分别查询每一列：

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

如果此表上的行太多，这可能不是最佳选择，因为它会为每个查询读取整个数据集，这意味着它会读取所有这些数据三次，而它只能读取一次。另一种选择是创建一个返回集合的函数：

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;
$$;

然后：

SELECT * FROM classes_transpose();