December 15, 2022Concatenating multiple rows into a single column dynamicallyHow 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.TeacherIDSubjectName1Biology1Maths1Physics2English2SocialThe 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 belowteacher_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 isSELECT 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 Like this:Like Loading...Related SQL SQL
I was wondering if you ever considered changing the layout of your website? It’s very well written; I love what you’ve got to say. But maybe you could a little more in the way of content, so people could connect with it better. You’ve got an awful lot of text for only having one or 2 pictures. Maybe you could space it out better?Loading...Reply