Search code examples
sqlpostgresqlpivotpostgresql-10

Postgres Convert Rows to Columns based on a Common field Using Crosstab

I have query select dist_name::text,service_name::text,status,count from mpr_application_status that returns

+---------------+--------------+----------------+-------+
| District Name | Service Name | Current Status | Count |
+---------------+--------------+----------------+-------+
| Dehradun      |          143 | APPROVED       |     1 |
| Dehradun      |         143  | PENDING        |     2 |
+---------------+--------------+----------------+-------+

I want to return a result set like this where we are converting rows into column based on the district name

+---------------+--------------+---------------+----------------+----------------+-------------+
| District Name | Service Name | ApprovedCount | Rejected Count | Pending  Count | Total Count |
+---------------+--------------+---------------+----------------+----------------+-------------+
| Dehradun      |          143 |             1 |              0 |              2 |           3 |
+---------------+--------------+---------------+----------------+----------------+-------------+

I have tried using crosstab function to return this desired table

SELECT *
FROM   crosstab(
   'select dist_name::text,service_name::text,count from mpr_application_status ORDER BY 1,2') 
   AS ct ("District Name" text, "Service Name" text,"Approved Count" bigint,"Pending Count" bigint, "Reject Count" bigint);

But encountering errors

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

Is there any way I can achieve this result on the Database end ? I am using postgresql 10

Solution

  • I think you can do this by using SUM and CASE WHEN:

    SELECT dist_name::text,service_name::text
,SUM(case when status = 'APPROVED' then count else 0 end) approved_count
,SUM(case when status = 'REJECTED' then count else 0 end) rejected_count
,SUM(case when status = 'PENDING' then count else 0 end) pending_count
,SUM(count) as total_count
FROM mpr_application_status
GROUP BY dist_name,service_name