CODEWITHSUNDEEP
oracle-databaseselectjoinsplitcase
user2691287
user2691287

Reputation: 1

Oracle Comma Separated Value (ID) in a Column. How to get Description for each Value in a Comma Separated string.

Sorry for the Confusing title.I myself did not understand it when i read it second time.

So here is the details description.

I have a table say "Awards" which have following Column: Name, Amount, Employee

and Another table "Employee" which have following column: Emp_Id, Emp_Name

and in employee column of "Awards" table i have value "01,20" which are actually the Employee ID referenced to "Employee" table.

So is there any way i can get Employee Name in select "Awards" query?

Upvotes: 0

Views: 2931

Answers (3)

user2691287
user2691287

Reputation: 1

I have changed the Database (added one more table). and already started changing the CODE, as for the said report i have used following 

WITH t AS
   (
      Select emp_name from  employee where  emp_id in (
        select regexp_substr(Employee ,'[^,]+', 1, level) from awards 
        connect by regexp_substr((select Employee from awards ), '[^,]+', 1, level) is
        not null)
   )
SELECT  LTRIM(SYS_CONNECT_BY_PATH(emp_name, ','),',') emp_name
FROM ( SELECT emp_name,
       ROW_NUMBER() OVER (ORDER BY emp_name) FILA
       FROM t )
WHERE CONNECT_BY_ISLEAF = 1
START WITH FILA = 1
CONNECT BY PRIOR FILA = FILA - 1

Which is temporary and i understand very less of it.

Thanks for you help and suggestion.

Upvotes: 0

Harshit
Harshit

Reputation: 560

Given below is the query to get comma seperated employee ids in form of rows which I put in subquery to get their name. Please edit as per your ewquirements. 

 Select Ename from  employee where employee_id in (
 SELECT trim(x.column_value.extract('e/text()')) COLUMNS
  from awards t, table (xmlsequence(xmltype('<e><e>' || replace(Employee,':','</e><e>')||  
  '</e></e>').extract('e/e'))) x   )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Here is one method:

select a.*, e.EmpName
from Awards a join
     Employees e
     on ','||a.employee||',' like '%,'||e.emp_id||',%';

This will return the employee names on separate lines. If you want them in a list, then you would need to concatenate them together (and the best function for doing that depends on your version of Oracle).

By the way, this is a very bad data structure, You should have an association table AwardEmployee that has one row for each row and each employee.

Upvotes: 1

Related Questions