CODEWITHSUNDEEP
sqlpostgresqlpivotpostgresql-13
Dmytro Sokolovskyi
Dmytro Sokolovskyi

Reputation: 25

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

Upvotes: 1

Views: 71

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

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

Upvotes: 1

Related Questions