By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE BPFinal( id INT, Partners VARCHAR(10), Branch VARCHAR(10), Amount INT, Date Date);
INSERT INTO BPFinal VALUES
(1001, 'ABC', 'BO1', 2000, '2020-11-30'),
(1001, 'ABC', 'BO2', 1500, '2020-11-30'),
(1002, 'XYZ', 'BO1', 4000, '2020-11-30'),
(1001, 'ABC', 'BO1', 5000, '2020-10-31')
Records: 4 Duplicates: 0 Warnings: 0
SET @sql = NULL;
SET @date = '2020-11-30';
SELECT GROUP_CONCAT(
CONCAT(
'SUM(CASE WHEN Partners = "', Partners,'" THEN Amount ELSE 0 END ) AS ',
Partners
)
)
INTO @sql
FROM ( SELECT DISTINCT Partners FROM BPFinal WHERE Date = @date ) AS b;
SET @sql = CONCAT('SELECT Branch,',@sql,
' FROM BPFinal
WHERE Date = "',@date,'"'
' GROUP BY Branch');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Statement prepared
| Branch | ABC | XYZ |
|---|---|---|
| BO1 | 2000 | 4000 |
| BO2 | 1500 | 0 |
SET @sql = NULL;
SET @date = '2020-10-31';
SELECT GROUP_CONCAT(
CONCAT(
'SUM(CASE WHEN Partners = "', Partners,'" THEN Amount ELSE 0 END ) AS ',
Partners
)
)
INTO @sql
FROM ( SELECT DISTINCT Partners FROM BPFinal WHERE Date = @date ) AS b;
SET @sql = CONCAT('SELECT Branch,',@sql,
' FROM BPFinal
WHERE Date = "',@date,'"'
' GROUP BY Branch');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Statement prepared
| Branch | ABC |
|---|---|
| BO1 | 5000 |