how do i do that format what should i use-CodePudding

schema:

create table candidates ( 
id int primary key, position 
varchar not null, 
salary int not null 
); 

insert into candidates values (3, 'senior', 35000); 
insert into candidates values (4, 'junior', 8000); 
insert into candidates values (5, 'senior', 30000); 
insert into candidates values (6, 'senior', 25000); 
insert into candidates values (7, 'junior', 30000); 
insert into candidates values (8, 'senior', 50000); 
insert into candidates values (9, 'senior', 30000); 
insert into candidates values (10, 'junior', 7000); 
insert into candidates values (11, 'junior', 8000); 
insert into candidates values (12, 'senior', 33000); 
insert into candidates values (13, 'junior', 5000); 
insert into candidates values (14, 'senior', 47000); 
insert into candidates values (15, 'junior', 12000);

CodePudding user response：

A simple case is one possible way:

select max(case when position='senior' then salary end) as 'senior',
       max(case when position='junior' then salary end) as 'junior'
from candidates;

https://dbfiddle.uk/b47Tz2qy

CodePudding user response：

You can use conditional aggregation

SELECT 
MAX(CASE WHEN position = 'senior' THEN salary ELSE 0 END) 'senior',
  MAX(CASE WHEN position = 'junior' THEN salary ELSE 0 END) 'junior'
FROM candidates

senior	junior
50000	30000

fiddle