How to get different columns in output by using the same column of a DB table in postgres?-CodePudding

The table and dummy data are:

create table employee(joining_date date, employee_type varchar, name character varying);

insert into employee values
('2022-11-16', 'Intern', 'ABBS'),
('11-11-2022', 'senior', 'ABcS'),
('12-11-2022', 'manager', 'ABDS'),
('11-11-2022', 'senior', 'AS'),
('12-11-2022', 'Intern', 'BBS');

What I want to get in the output is joining_date as date1 when employee_type is 'Intern', joining_date as date2 when employee_type is 'Manager', joining_date as date3, when employee_type is 'Senior' and name is 'ABcS'.

I have tried using inner joins.

select t1.joining_date as date1, 
 t2.joining_date as date2,  t3.joining_date as date3
from employee t inner join employee t1 on t1.employee_type = t.employee_type and t.employee_type = 'Intern'
inner join employee t2 on t2.employee_type = t.employee_type and t.employee_type ='Manager'
inner join employee t3 on t3.employee_type = t.employee_type and t.employee_type = 'Senior' and t3.name = t.name and t.name = 'ABcS'

and

select t1.joining_date as date1, 
 t2.joining_date as date2,  t3.joining_date as date3
from employee t inner join employee t1 on t1.employee_type = 'Intern'
inner join employee t2 on t2.employee_type = 'Manager'
inner join employee t3 on t3.employee_type = 'Senior' and t3.name = 'ABcS'

I am obtaining no data in my table. how to get output in the tables and NULL if no data is available?

CodePudding user response：

SELECT X.* FROM
(
  SELECT T.employee_type ,T.NAME,
    CASE
      WHEN T.employee_type='Intern' THEN T.joining_date 
      ELSE NULL
    END AS DATE_1,
    CASE
      WHEN T.employee_type='Manager'THEN T.joining_date 
      ELSE NULL
    END AS DATE_2
    FROM YOUR_TABLE AS T
)AS X WHERE X.DATE_1 IS NOT NULL OR X.DATE_2 IS NOT NULL

May be something like this?