Combining a result in Oracle SQL in a DISTINCT SELECT

Question

I have this requirement where I have select the distinct order_no, code, display_order, level, description and then match its patterns.

I currently have this sql statement

 SELECT DISTINCT order_no, code, disp_order, lvl, description, 
CASE WHEN pattern='1' THEN supplier_code ELSE NULL END AS pattern1,
CASE WHEN pattern='2' THEN supplier_code ELSE NULL END AS pattern2,
CASE WHEN pattern='3' THEN supplier_code ELSE NULL END AS pattern3, 
CASE WHEN pattern='4' THEN supplier_code ELSE NULL END AS pattern4, 
CASE WHEN pattern='5' THEN supplier_code ELSE NULL END AS pattern5  
from tbl_pattern

which gives me this result set

+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+
| order_no | code | disp_order | lvl | description | pattern1 | pattern2 | pattern3 | pattern4 | pattern5 |
+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |          |          |          |          |   0821   |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |   1021   |          |          |          |          |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |          |   4547   |          |          |          |
| RM001-01 | 1    | 2          | 3   | HK140904-1C |   4547   |          |          |          |          |
| RM001-01 | 1    | 3          | 3   | HK140904-1D |          |          |          |   3540   |          |
| RM001-01 | 1    | 4          | 2   | HK140904-1E |          |          |          |          |          |
+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+

but what I want to achieve is like this:

+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+
| order_no | code | disp_order | lvl | description | pattern1 | pattern2 | pattern3 | pattern4 | pattern5 |
+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |          |          |          |          |   0821   |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |   1021   |   4547   |          |          |          |
| RM001-01 | 1    | 2          | 3   | HK140904-1C |   4547   |          |          |          |          |
| RM001-01 | 1    | 3          | 3   | HK140904-1D |          |          |          |   3540   |          |
| RM001-01 | 1    | 4          | 2   | HK140904-1E |          |          |          |          |          |
+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+

Sample data in table

+----------+------+------------+-----+-------------+---------+---------------+
| order_no | code | disp_order | lvl | description | pattern | supplier_code | 
+----------+------+------------+-----+-------------+---------+---------------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |     5   |     0821      |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |     1   |     1021      |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |     2   |     4547      |
| RM001-01 | 1    | 2          | 3   | HK140904-1C |     1   |     4547      |
| RM001-01 | 1    | 3          | 3   | HK140904-1D |     4   |     3540      |
| RM001-01 | 1    | 4          | 2   | HK140904-1E |         |               |
+----------+------+------------+-----+-------------+---------+---------------+

Answer 1

Try like below

SELECT  order_no, code, disp_order, lvl, description, 
    max(CASE WHEN pattern='1' THEN supplier_code ELSE NULL END) AS pattern1,
    max( CASE WHEN pattern='2' THEN supplier_code ELSE NULL END) AS pattern2,
    max(CASE WHEN pattern='3' THEN supplier_code ELSE NULL END ) AS pattern3, 
    max( CASE WHEN pattern='4' THEN supplier_code ELSE NULL END) AS pattern4, 
    max( CASE WHEN pattern='5' THEN supplier_code ELSE NULL END) AS pattern5  
    from tbl_pattern
    group by order_no, code, disp_order, lvl, description