SQL: Combining multiple rows to a single row based on non primary key-CodePudding

I've a table like this:

|Id   | order_id | product_id | quantity |
|1    | A        | 111        | 2        | 
|2    | A        | 112        | 3        | 
|3    | A        | 113        | 1        | 
|4    | C        | 112        | 2        | 
|5    | B        | 111        | 5        | 
|6    | B        | 112        | 1        |

And I'm expecting the table as:

| order_id | quantity_111  | quantity_112  | quantity_113 |
| A        | 2             | 3             |  1           |
| C        | null          | 2             |  null        |
| B        | 5             | 1             |  null        |

Where we will make quantity columns with product ids, we only have 3 fixed product ids.

Kindly help me figure out the SQL query to achieve this output.

CodePudding user response：

You can do a poor man's pivot. For example:

select
  order_id,
  max(case when product_id = 111 then quantity end) as q111,
  max(case when product_id = 112 then quantity end) as q112,
  max(case when product_id = 113 then quantity end) as q113
from t
group by order_id