My table structure:
cust_id | action | action_count
--------+----------+-------------
1 | Approved | 15
2 | Approved | 25
1 | Rejected | 6
2 | Pending | 20
2 | Rejected | 7
I have to get the query result as:
cust_id | Approved | Pending | Rejected
--------+----------+---------+---------
1 | 15 | 0 | 6
2 | 25 | 20 | 7
Try this query
select
cust_id,
max(if(action='Approved', action_count, 0)) as Approved,
max(if(action='Rejected', action_count, 0)) as Rejected,
max(if(action='Pending', action_count, 0)) as Pending
from
tbl
group by
cust_id
| CUST_ID | APPROVED | REJECTED | PENDING |
-------------------------------------------
| 1 | 15 | 6 | 0 |
| 2 | 25 | 7 | 20 |
Here's a static query if the number of action is known.
SELECT cust_id,
COALESCE(MAX(CASE WHEN action = 'Approved' THEN action_Count END), 0) Approved,
COALESCE(MAX(CASE WHEN action = 'Pending ' THEN action_Count END), 0) Pending ,
COALESCE(MAX(CASE WHEN action = 'Rejected' THEN action_Count END), 0) Rejected
FROM TableName
GROUP BY cust_id
But if the number of value of action is unknown, a dynamic sql is much more preferred.
SELECT GROUP_CONCAT(DISTINCT
CONCAT('COALESCE(MAX(CASE WHEN action = ''',
action,
''' THEN action_Count END), 0) AS ',
CONCAT('`', action, '`')
)) INTO @sql
FROM TableName;
SET @sql = CONCAT('SELECT cust_id, ', @sql, '
FROM TableName
GROUP BY cust_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
