I have this data
I want to have name,jan,feb,mar columns. i used Case to pivot like this
select name,
CASE WHEN datepaid LIKE '%-01-%' THEN amountpaid ELSE 0 END AS jan,
CASE WHEN datepaid LIKE '%-02-%' THEN amountpaid ELSE 0 END AS feb,
CASE WHEN datepaid LIKE '%-03-%' THEN amountpaid ELSE 0 END AS mar
FROM payments;
I DO NOT WANT THE NAME
to repeat.
When I group by name
I end up with the last records of the respective names as shown
select name,
CASE WHEN datepaid LIKE '%-01-%' THEN amountpaid ELSE 0 END AS jan,
CASE WHEN datepaid LIKE '%-02-%' THEN amountpaid ELSE 0 END AS feb,
CASE WHEN datepaid LIKE '%-03-%' THEN amountpaid ELSE 0 END AS mar
FROM payments
group by name;
Kindly help Here is SQL DATA.
CREATE TABLE `payments` (
`datepaid` date DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`amountpaid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `payments` (`datepaid`, `name`, `amountpaid`) VALUES
('2020-01-13', 'james', 150),
('2020-01-14', 'mercy', 150),
('2020-01-16', 'otieno', 150),
('2020-01-17', 'njoroge', 100),
('2020-02-18', 'james', 150),
('2020-02-20', 'mercy', 150),
('2020-02-21', 'otieno', 150),
('2020-03-15', 'william', 50),
('2020-03-19', 'njoroge', 100),
('2020-03-22', 'william', 50),
('2020-03-23', 'njoroge', 100);