Hi All,
I have a table with departments and employees holding employee name and assignment to a department. I want to have a table where on one row would be employee name and all departments.
If an employee is member of that department then I want to display his posiiton inside of this department, if he is not member of that department then I want to display '-'.
I have following example:
CREATE TABLE DEPT(
ID NUMBER(3),
NAME VARCHAR2(10),
TYPE NUMBER(3)
);
CREATE TABLE EMP(
FIRST_NAME VARCHAR2(10),
STATE VARCHAR2(10),
ID_DEPT NUMBER(3)
);
INSERT INTO DEPT(ID, NAME, TYPE) VALUES (1, 'Dept_1', 1);
INSERT INTO DEPT(ID, NAME, TYPE) VALUES (2, 'Dept_2', 1);
INSERT INTO DEPT(ID, NAME, TYPE) VALUES (3, 'Dept_3', 1);
INSERT INTO DEPT(ID, NAME, TYPE) VALUES (4, 'Dept_4', 1);
INSERT INTO DEPT(ID, NAME, TYPE) VALUES (5, 'Dept_5', 1);
INSERT INTO DEPT(ID, NAME, TYPE) VALUES (6, 'Dept_6', 2);
INSERT INTO DEPT(ID, NAME, TYPE) VALUES (7, 'Dept_5', 1);
INSERT INTO EMP(FIRST_NAME, STATE, ID_DEPT) VALUES('John 1', 'New', 1);
INSERT INTO EMP(FIRST_NAME, STATE, ID_DEPT) VALUES('John 2', 'Senior', 2);
INSERT INTO EMP(FIRST_NAME, STATE, ID_DEPT) VALUES('Jenn', 'New', 2);
INSERT INTO EMP(FIRST_NAME, STATE, ID_DEPT) VALUES('Ted', 'New', 5);
INSERT INTO EMP(FIRST_NAME, STATE, ID_DEPT) VALUES('Petr 1', 'Junior', 3);
INSERT INTO EMP(FIRST_NAME, STATE, ID_DEPT) VALUES('Petr 2', 'New', 1);
INSERT INTO EMP(FIRST_NAME, STATE, ID_DEPT) VALUES('Petr 3', 'New', 1);
COMMIT;
If I run following query:
SELECT *
FROM (SELECT NAME, FIRST_NAME, NVL(STATE, '-') ST, ID_DEPT
FROM (SELECT ID, NAME
FROM DEPT
WHERE TYPE = 1) DEPARTMENT LEFT JOIN
(SELECT FIRST_NAME, STATE, ID_DEPT
FROM EMP) EMPLOYEE ON DEPARTMENT.ID = EMPLOYEE.ID_DEPT)
PIVOT (MIN(ST) FOR NAME IN ('Dept_1', 'Dept_2', 'Dept_3', 'Dept_4', 'Dept_5'))
WHERE FIRST_NAME IS NOT NULL;
I anyway get nulls in rows where an employee is not member of specific department, why?
Thank you in advance.