Search code examples
sqlpostgresqlpivottranspose

Pivoting a table with SQL

I have a table with position (junior, senior), salary, and an ID. I have done the following to find the highest salary for each position.

SELECT position, MAX(salary) FROM candidates GROUP BY position;

What I am getting: enter image description here

How I want it: enter image description here

I want to transpose the outcome so that 'junior' and 'senior' are the columns without using crosstab. I have looked at many pivot examples but they are done on examples much more complex than mine.

Solution

  • I am not proficient in PostgreSQL, but I believe there is a practical workaround solution since this is a simple table:

    SELECT 
    max(case when position = 'senior' then salary else null end) senior,
    max(case when position = 'junior' then salary else null end) junior
FROM payments

    It worked with this example:

    create table payments (id integer, position varchar(100), salary int);
        insert into payments (id, position, salary) values (1, 'junior', 1000);
        insert into payments (id, position, salary) values (1, 'junior', 2000);
        insert into payments (id, position, salary) values (1, 'junior', 5000);
        insert into payments (id, position, salary) values (1, 'junior', 3000);
        insert into payments (id, position, salary) values (2, 'senior', 3000);
        insert into payments (id, position, salary) values (2, 'senior', 8000);
        insert into payments (id, position, salary) values (2, 'senior', 9000);
        insert into payments (id, position, salary) values (2, 'senior', 7000);
        insert into payments (id, position, salary) values (2, 'senior', 4000);
        select 
            max(case when position = 'junior' then salary else 0 end) junior,
            max(case when position = 'senior' then salary else 0 end) senior
        from payments;