How to concatenate multiple rows of a column in a table into a single column?

I have to concatenate multiple rows to a single column. For example, consider the below teacher’s table.

The above table is a normalized table containing the subjects and teacher ID. We will denormalize the table, by concatenating the subjects of each teacher into a single column, and thus preserving the teacher ID as unique in the output. The output data should look like as below

teacher_id subjects_list
-------------------------------
     1     Biology|Maths|Physics
     2     English|Social

How to achieve this?

Solution:

We can concatenate multiple rows in to a single column dynamically by using the Hierarchical query. The SQL query to get the result is

SELECT teacher_id,
       SUBSTR(SYS_CONNECT_BY_PATH(subject_name, '|'),2) 
           subjects_list
FROM 
(
   SELECT  teacher_id,
           subject_name,
           COUNT(*) OVER (PARTITION BY teacher_id) sub_cnt,
           ROW_NUMBER () OVER (PARTITION BY teacher_id
                    ORDER BY subject_name) sub_seq
   FROM    teachers
) A
WHERE             sub_seq=sub_cnt
START WITH        sub_seq=1
CONNECT BY prior  sub_seq+1=sub_seq
AND prior         teacher_id=teacher_id