Sometime we need to show row data as a column so this is possible by using case on mysql database.
A MySQL pivot table is a way to transform data from a normalized format (multiple rows and columns) into a more denormalized format (a single row with multiple columns). This can be useful when you need to display data in a specific format or perform calculations on it.
A pivot table can be created using an SQL query with a combination of the GROUP BY and CASE statements.
First I create a tbl_employee
CREATE TABLE `tbl_employee` (
`id` int(11) NOT NULL,
`emp_code` varchar(4) NOT NULL,
`month` varchar(3) NOT NULL,
`salary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `tbl_employee`
ADD PRIMARY KEY (`id`);
ALTER TABLE `tbl_employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
Now Insert some data
INSERT INTO `tbl_employee` (`id`, `emp_code`, `month`, `salary`) VALUES
(1, '1001', 'Jan', 60000),
(2, '1002', 'Jan', 50000),
(3, '1001', 'Feb', 61000),
(4, '1002', 'Feb', 51000),
(5, '1001', 'Mar', 62000),
(6, '1002', 'Mar', 52000),
(7, '1001', 'Apr', 63000),
(8, '1002', 'Apr', 53000);
Now table looking looks like
SELECT * FROM `tbl_employee`
Now I want to month name show on column so run below query
SELECT employee.emp_code,
MAX(CASE WHEN employee.month = "Jan" THEN employee.salary END) "Jan",
MAX(CASE WHEN employee.month = "Feb" THEN employee.salary END) "Feb",
MAX(CASE WHEN employee.month = "Mar" THEN employee.salary END) "Mar",
MAX(CASE WHEN employee.month = "Apr" THEN employee.salary END) "Apr"
FROM tbl_employee as employee
group by employee.emp_code
Now output looks like
