MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


CREATE TABLE `EMPLOYEES` (
  `EMP_ID` int DEFAULT NULL,
  `EMP_NAME` varchar(50) DEFAULT NULL
);
CREATE TABLE `ALLOCATION` (
  `ALLO_ID` int,
  `ALLO_DETAILS` VARCHAR(100),
  `ALLO_AMOUNT` int
);
CREATE TABLE `DEDUCATION` (
  `DED_ID` int DEFAULT NULL,
  `DED_DETAILS` varchar(100) DEFAULT NULL,
  `DED_AMOUNT` int DEFAULT NULL
);
CREATE TABLE `SALARY` (
  `SAL_ID` int DEFAULT NULL,
  `EMP_ID` int DEFAULT NULL,
  `ALLO_ID` int DEFAULT NULL,
  `DED_ID` int DEFAULT NULL
);
INSERT INTO EMPLOYEES VALUES ( 1, 'EmpA' );
INSERT INTO EMPLOYEES VALUES ( 2, 'EmpB' );

INSERT INTO ALLOCATION VALUES ( 1, 'Basic Salary', 2000 );
INSERT INTO ALLOCATION VALUES ( 2, 'Earnings', 1000 );
INSERT INTO ALLOCATION VALUES ( 3, 'Extra Hours', 500 );
INSERT INTO ALLOCATION VALUES ( 4, 'Dangerous Work', 1000 );
INSERT INTO ALLOCATION VALUES ( 5, 'Basic Salary', 3000 );
INSERT INTO ALLOCATION VALUES ( 6, 'Earnings', 500 );
INSERT INTO ALLOCATION VALUES ( 7, 'Extra Hours', 500 );

INSERT INTO DEDUCATION VALUES ( 1, 'Bank A', 1000 );
INSERT INTO DEDUCATION VALUES ( 2, 'Tax', 200 );
INSERT INTO DEDUCATION VALUES ( 3, 'Retirement', 500 );

INSERT INTO DEDUCATION VALUES ( 4, 'Bank A', 1000 );
INSERT INTO DEDUCATION VALUES ( 5, 'Tax', 200 );
INSERT INTO DEDUCATION VALUES ( 6, 'Retirement', 500 );

INSERT INTO SALARY VALUES ( 1, 1, 1, 1 );
INSERT INTO SALARY VALUES ( 2, 1, 2, 2 );
INSERT INTO SALARY VALUES ( 3, 1, 3, 3 );
INSERT INTO SALARY VALUES ( 4, 1, 4, NULL );

INSERT INTO SALARY VALUES ( 5, 2, 5, 4 );
INSERT INTO SALARY VALUES ( 6, 2, 6, 5 );
INSERT INTO SALARY VALUES ( 7, 2, 7, 6 );
SELECT 
  EMP_NAME, ALLO_DETAILS, ALLO_AMOUNT, DED_DETAILS, DED_AMOUNT
FROM EMPLOYEES a
JOIN SALARY b ON a.EMP_ID = b.EMP_ID
LEFT JOIN ALLOCATION c ON b.ALLO_ID = c.ALLO_ID
LEFT JOIN DEDUCATION d ON b.DED_ID = d.DED_ID
ORDER BY EMP_NAME, ALLO_DETAILS, DED_DETAILS


EMP_NAME
ALLO_DETAILS
ALLO_AMOUNT
DED_DETAILS
DED_AMOUNT


EmpA
Basic Salary
2000
Bank A
1000

EmpA
Dangerous Work
1000
null
null

EmpA
Earnings
1000
Tax
200

EmpA
Extra Hours
500
Retirement
500

EmpB
Basic Salary
3000
Bank A
1000

EmpB
Earnings
500
Tax
200

EmpB
Extra Hours
500
Retirement
500


SELECT 
  MAX(EMP_NAME),
  SUM(CASE WHEN c.ALLO_DETAILS = 'Basic Salary' THEN c.ALLO_AMOUNT ELSE 0 END) "Basic Salary",
  SUM(CASE WHEN c.ALLO_DETAILS = 'Earnings' THEN c.ALLO_AMOUNT ELSE 0 END) "Earnings",
  SUM(CASE WHEN c.ALLO_DETAILS = 'Extra Hours' THEN c.ALLO_AMOUNT ELSE 0 END) "Extra Hours",
  SUM(CASE WHEN c.ALLO_DETAILS = 'Dangerous Work' THEN c.ALLO_AMOUNT ELSE 0 END) "Dangerous Work",
  SUM(COALESCE(c.ALLO_AMOUNT, 0)) "Total Allocation",
  SUM(CASE WHEN d.DED_DETAILS = 'Bank A' THEN d.DED_AMOUNT ELSE 0 END) "Bank A",
  SUM(CASE WHEN d.DED_DETAILS = 'Tax' THEN d.DED_AMOUNT ELSE 0 END) "Tax",
  SUM(CASE WHEN d.DED_DETAILS = 'Retirement' THEN d.DED_AMOUNT ELSE 0 END) "Retirement",
  SUM(COALESCE(d.DED_AMOUNT, 0)) "Total Deduction",
  SUM(COALESCE(c.ALLO_AMOUNT, 0)) - SUM(COALESCE(d.DED_AMOUNT, 0)) "Salary"
FROM EMPLOYEES a
JOIN SALARY b ON a.EMP_ID = b.EMP_ID
LEFT JOIN ALLOCATION c ON b.ALLO_ID = c.ALLO_ID
LEFT JOIN DEDUCATION d ON b.DED_ID = d.DED_ID
GROUP BY a.EMP_ID


MAX(EMP_NAME)
Basic Salary
Earnings
Extra Hours
Dangerous Work
Total Allocation
Bank A
Tax
Retirement
Total Deduction
Salary


EmpA
2000
1000
500
1000
4500
1000
200
500
1700
2800

EmpB
3000
500
500
0
4000
1000
200
500
1700
2300
EMP_NAME	ALLO_DETAILS	ALLO_AMOUNT	DED_DETAILS	DED_AMOUNT
EmpA	Basic Salary	2000	Bank A	1000
EmpA	Dangerous Work	1000	null	null
EmpA	Earnings	1000	Tax	200
EmpA	Extra Hours	500	Retirement	500
EmpB	Basic Salary	3000	Bank A	1000
EmpB	Earnings	500	Tax	200
EmpB	Extra Hours	500	Retirement	500