How do i pivot PostgreSQL table?

I have table company_representatives which looks like that:

enter image description here

Create table script:

CREATE TABLE IF NOT EXISTS company_representatives (
 _id          integer NOT NULL,
 name         varchar(50) NOT NULL,
 surname      varchar(100) NOT NULL,
 date_of_join date NOT NULL,
 role         varchar(250) NOT NULL,
 company_id   integer NOT NULL,
 CONSTRAINT PK_company_representatives PRIMARY KEY ( _id ),
 CONSTRAINT FK_144 FOREIGN KEY ( company_id ) REFERENCES companies ( _id )

INSERT INTO company_representatives VALUES
(1,'random name','random surname', '2001-01-23', 'CEO', 1),
(2,'next random name','next random surname', '2001-01-23', 'Co-founder', 1),
(3,'John','Doe', '2003-02-12', 'HR', 1),
(4,'Bread','Pitt', '2001-01-23', 'Security officer', 1),
(5,'Toast','Malone', '1997-11-05', 'CEO', 2),

I need to pivot this table to make it's columns look like that:

company_id | CEO | Co-Founder | HR | Security Officer
    1         1         2       3            4          "_id of company's representatives"
    2         5         6       7            8
    3         9        10       11          12

1 answer

  • answered 2021-06-23 07:29 Jim Jones

    You can simply use FILTER directly in the SELECT clause:

    SELECT DISTINCT ON (company_id)
      count(*) FILTER (WHERE role = 'CEO') AS CEO,
      count(*) FILTER (WHERE role = 'Co-founder') AS "Co-Founder",
      count(*) FILTER (WHERE role = 'HR') AS HR,
      count(*) FILTER (WHERE role = 'Security officer') AS "Security Officer"
    FROM company_representatives
    GROUP BY company_id;

    In question it is not clear what the values attached to the roles actually mean, so I assumed you just want to count them. If not, just change it to other aggregate function.

    EDIT (see comments): pivot table using crosstab, assuming there is one record for each role in all companies:

    SELECT *
    FROM crosstab(
     'SELECT company_id,  _id, name
      FROM company_representatives ORDER BY company_id,role' 
    ) AS ct(company_id integer,ceo text,co_founder text,hr text,security_officer text);

    Demo: db<>fiddle