Rows and Columns - Mixed

[jrahma]

Hi,

I have below SQL in MySQL:

SELECT employees.employee_number, employees.official_name, job_titles.job_title_name, employee_banks.bank_iban, employees.last_joining_date, payroll_category.payroll_category_name, SUM(payroll_transactions.payroll_amount) AS net_amount
FROM payroll_transactions
JOIN employees ON employees.employee_id = payroll_transactions.employee_id
JOIN employee_job ON employee_job.employee_id = employees.employee_id
JOIN job_titles ON job_titles.job_title_id = employee_job.job_title
LEFT JOIN employee_banks ON employee_banks.employee_id = payroll_transactions.employee_id
JOIN payroll_category ON payroll_category.payroll_category_id = payroll_transactions.payroll_category
GROUP BY payroll_transactions.payroll_id, payroll_transactions.payroll_category;

it returns the employee payroll as rows

I want to the columns to be employees.employee_number, employees.official_name, job_titles.job_title_name, employee_banks.bank_iban, employees.last_joining_date

and then I want every payroll_category.payroll_category_name to be a column with the total as rows

here is an example:

Emp# - Name            - Title          - Bank        - Joined        - Basic - Car Allowance - Rent Allowance - Total
12      - John Mathews - IT Manager - HSBC1234 - 12-12-2016 - 1000  - 50                - 700                 - 1750
12      - Mathews John - AE Manager - HSBC5678 - 10-10-2016 - 1100 - 10                -                       - 1010