Hi All,
I have a task to concatenate two columns below SID - SNAME.
Input parameter is :sid. We can pass multiple parameters to :sid
CREATE TABLE TABLE1 ( SID NUMBER, SNAME VARCHAR2(100));
Insert into TABLE1 (SID,SNAME) values (1,'SURF'S UP');
Insert into TABLE1 (SID,SNAME) values (2,'GNLV STYLE & KEY');
Insert into TABLE1 (SID,SNAME) values (3,'ESSENTIALS NORTH & SOUTH');
Insert into TABLE1 (SID,SNAME) values (4,'D'Ora');
select * from TABLE1;
| SID | SNAME |
|---|
| 1 | SURF'S UP |
| 2 | GNLV STYLE & KEY |
| 3 | ESSENTIALS NORTH & SOUTH |
| 4 | D'Ora |
Expected Output
1. with parameter input :sid in (1,2)
Output : 1 - SURF'S UP , 2 - GNLV STYLE & KEY
2. with parameter input : sid in (1,2,3,4)
Output: 1 - SURF'S UP , 2 - GNLV STYLE & KEY , 3 - ESSENTIALS NORTH & SOUTH , 4 - D'Ora
I tried using the below query,
select (select RTRIM ( xmlagg (xmlelement (c, sid ||' - '||sname||',') ).extract ('//text()'), ',' )
AS concatenated FROM table1 where ',' || :para || ',' like '%,'|| sid || ',%') as concat from dual ;
Output what i am getting is as below :
1 - SURF'S UP , 2 - GNLV STYLE & TREND
I am not handling apstrophe and ampersand correctly.
Can you please help me.
Thanks,