How do i pivot PostgreSQL table?

Question

I have table company_representatives which looks like that:

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

Please provide a data sample as text in your question, not as image. And your table definition, too (`CREATE TABLE` script). — Erwin Brandstetter, Jun 23 '21 at 05:39
This has been answered many times before. You might want to try one [of the many questions](https://stackoverflow.com/search?q=%5Bpostgresql%5D+and+%28%5Bpivot%5D+or+%5Bcrosstab%5D%29) and then show us your code when you are stuck. — , Jun 23 '21 at 05:44

Jim Jones · Accepted Answer · 2021-06-23T09:15:58.927

You can simply use FILTER directly in the SELECT clause:

SELECT DISTINCT ON (company_id)
  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

In my question every company has 1 person of each categories, so i want to output _id of every person related to category in company_id. Maybe instead of count(*) i shoud write _id. — Dmytro Sokolovskyi, Jun 23 '21 at 08:22
@DmytroSokolovskyi I see. So my last edit might interest you :) — Jim Jones, Jun 23 '21 at 08:52

How do i pivot PostgreSQL table?

1 Answers1