Search code examples
sqlpostgresqlpivotgreenplum

pivoting table in postgresql


what i have

customerid   status

   Ax         1    
   Bx         3
   Cx         5
   Dx         4
   Ex         2

i am looking to pivot above table.

What i need

customerid   status_1 status_2 status_3 status_4 status_5

   Ax         1         0         0        0        0
   Bx         0         0         1        0        0
   Cx         0         0         0        0        1
   Dx         0         0         0        1        0
   Ex         0         1         0        0        0

Solution

  • select customerid, 
        case when status = 1 then 1 else 0 end as status_1,
        case when status = 2 then 1 else 0 end as status_2,
        case when status = 3 then 1 else 0 end as status_3,
        case when status = 4 then 1 else 0 end as status_4,
        case when status = 5 then 1 else 0 end as status_5
    from your_table
    order by customerid;