Create ORACLE Query to Get Concated string Values from relational table

Below Query will help to get the requery result from concated string.

Create Table Schema

  CREATE TABLE "TBL_VKYC_CRMDETAILS" 

   (

   "ID" NUMBER(10,0) NOT NULL ENABLE, 

"PRODUCTTYPE" VARCHAR2(50 BYTE) NOT NULL ENABLE, 

"REFNO" VARCHAR2(50 BYTE) NOT NULL ENABLE, 

"CRMCASENO" VARCHAR2(50 BYTE) NOT NULL ENABLE, 

"CREATEDDATE" VARCHAR2(50 BYTE) NOT NULL ENABLE, 

"SQSREFID" VARCHAR2(100 BYTE) DEFAULT NULL, 

"CRMDOCRESPONSEID" VARCHAR2(100 BYTE) DEFAULT NULL, 

"SQSFILENAME" VARCHAR2(2000 BYTE) DEFAULT NULL

   );

CREATE SEQUENCE  SEQ_TBL_VKYC_CRMDETAILS

MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER TBL_VKYC_CRMDETAILS_SEQ 

BEFORE INSERT ON TBL_VKYC_CRMDETAILS 

FOR EACH ROW

BEGIN

  SELECT SEQ_TBL_VKYC_CRMDETAILS.NEXTVAL

  INTO   :new.id

  FROM   dual;

END;

/

ALTER TRIGGER TBL_VKYC_CRMDETAILS_SEQ ENABLE;

--================

Like string is concatenated with comma seperate string/value, get the comma seperate value from relational table in rows, later get values for all rows and again convert into single column on ORACLE

Create table tblFirst(Id int, Name VARCHAR2(50), CompanyIds varchar2(50))

insert into tblFirst(id, Name, CompanyIds) values (1, 'Murli', '1')

insert into tblFirst(id, Name, CompanyIds) values (2, 'User 1', '1,2')

insert into tblFirst(id, Name, CompanyIds) values (3, 'User 2', '1,2')

insert into tblFirst(id, Name, CompanyIds) values (4, 'User 3', '1,2,3')

insert into tblFirst(id, Name, CompanyIds) values (5, 'User 0', '1,2')

select * from tblFirst

Commit

--------------------------------

Create table tblCompany(Id int, CompanyName varchar2(50))

insert into tblCompany(id, CompanyName) values (1, 'Company A');

insert into tblCompany(id, CompanyName) values (2, 'Company B');

insert into tblCompany(id, CompanyName) values (3, 'Company C');

select * from tblCompany

Commit

------------------------------

select t1.*, (select  listagg(CompanyName,',')  WITHIN GROUP (ORDER BY 1) --product_variant_name, product_category,product_variant_id

from tblCompany C 

where C.id in

  (select regexp_substr(t1.CompanyIds,'[^,]+', 1, level) 

   from dual 

   connect BY regexp_substr(t1.CompanyIds, '[^,]+', 1, level) 

   is not null)) "Company Names" from tblFirst t1

order by t1.id desc

Output will look like this