Pivot Row on PostgreSQL

问题:

I have a query that returns an entire row and I need to pivot this result into a new table.

SELECT id_no, stud_name, group_no, class_1, class_2, class_3, class_4 FROM tbl_stud_class

This returns the following:

| id_no | stud_name | group_no | class_1 | class_2 | class_3 | class 4 |
| 1     | John Doe  | A11      | 84      | 60      | 80      | 79      |

I need to be able to return this row as:

| id_no | stud_name | group_no | class   | grade |
| 1     | John Doe  | A11      | class_1 | 84    |
| 1     | John Doe  | A11      | class_2 | 60    |
| 1     | John Doe  | A11      | class_3 | 80    |
| 1     | John Doe  | A11      | class_4 | 79    |

Can someone point me to a way to do this please? I am very new to PostgreSQL so I have no idea where and how to start.

Thank you!

回答1:

You can also use LATERAL in postgresql 9.3+

SELECT id_no, stud_name, group_no, class, grade
FROM   tbl_stud_class 
       CROSS JOIN LATERAL ( VALUES
                     ('class_1', class_1),
                     ('class_2', class_2),
                     ('class_3', class_3),
                     ('class_4', class_4)
            ) l(class, grade)

回答2:

Need this:

WITH tbl_stud_class
     (id_no, stud_name, group_no, class_1, class_2, class_3, class_4) AS
(
    VALUES
      (1, 'John Doe', 'All', 84, 60, 80, 79),
      (2, 'Aberel Dalton', 'Some', 75, 32, NULL, 80)
)

SELECT
    id_no, stud_name, group_no, 'class_1' AS class, class_1 AS grade
FROM
    tbl_stud_class
WHERE 
    class_1 IS NOT NULL
UNION
SELECT
    id_no, stud_name, group_no, 'class_2' AS class, class_2 AS grade 
FROM
    tbl_stud_class
WHERE 
    class_2 IS NOT NULL
UNION
SELECT
    id_no, stud_name, group_no, 'class_3' AS class, class_3 AS grade
FROM
    tbl_stud_class
WHERE 
    class_3 IS NOT NULL
UNION
SELECT
    id_no, stud_name, group_no, 'class_4' AS class, class_4 AS grade
FROM
    tbl_stud_class 
WHERE 
    class_4 IS NOT NULL 
ORDER BY
    id_no, class ;

AFAIK, there is no way to do this in an automatic way. PostgreSQL has the crosstab function, which does PIVOT, but it works the other way around (from your desired output to your input).

---

You have a shorter alternative, using arrays and unnest:

WITH tbl_stud_class
     (id_no, stud_name, group_no, class_1, class_2, class_3, class_4) AS
(
    VALUES
      (1, 'John Doe', 'All', 84, 60, 80, 79),
      (2, 'Aberel Dalton', 'Some', 75, 32, NULL, 80)
)

SELECT
    *
FROM
(
    SELECT
        id_no, stud_name, group_no, 
        unnest(ARRAY['class_1', 'class_2', 'class_3', 'class_4']) AS class,
        unnest(ARRAY[ class_1,   class_2,   class_3,   class_4 ]) AS grade
    FROM
        tbl_stud_class
) AS s0        
WHERE
    grade is not null 
ORDER BY
    id_no, class ;