Thanks for the question, Mohini.

Asked: August 22, 2017 - 6:54 pm UTC

Last updated: August 24, 2017 - 12:59 am UTC

Version: Oracle11g

Viewed 10K+ times! This question is

You Asked

Hi Team,

I wanted to share a LiveSQL link but unfortunately I created the test case and somehow I lost the link.

Here is my test case in documented format.

- I have two tables
- create table employee(empid number, empname varchar2(20), deptname varchar2(50));
- create table dept(deptid number, deptname varchar2(50));

- Information Stored in table is like this

Employee
Empid EmpName deptname
1 James 1,2,3
2 Tom 3,4

Dept
deptid deptname
1 Testing
2 Developer
3 Analyst
4 Designer

So I want to write a query which gives me the following output:

Empid EmpName deptname
1 James Testing, Developer, Analyst
2 Tom Analyst, Designer

I made it using MYSQL, now wanted to do the same in ORACLE as well.

- In mysql I used GROUP_CONCAT() and FIND_IN_SET() :

SELECT e.empname, GROUP_CONCAT(b.deptName ORDER BY b.deptID) AS deptname FROM employee ed INNER JOIN dept b
ON FIND_IN_SET(b.deptName , ca.deptID) > 0 GROUP BY e.empid

Kindly help me on this. Sooner will be better as It's an high priority task for me.
Thanks in advance.

and Connor said...


SQL> create table emp ( eid int, en varchar2(10), d varchar2(20));

Table created.

SQL> insert into emp values (1,'Tom','1,2');

1 row created.

SQL> insert into emp values (2,'Mike','2,3');

1 row created.

SQL> insert into emp values (3,'Sue','3');

1 row created.

SQL>
SQL> create table dept ( did int, dn varchar2(20));

Table created.

SQL>
SQL> insert into dept values (1,'Sales');

1 row created.

SQL> insert into dept values (2,'HR');

1 row created.

SQL> insert into dept values (3,'Testing');

1 row created.

SQL> insert into dept values (4,'Admin');

1 row created.

SQL>
SQL> select eid,en,
  2
SQL>
SQL>
--
-- first we'll convert the list to rows
--
SQL>
SQL> with dept_as_rows as
  2  (
  3  select eid,en,
  4         substr( ','||d||',' ,
  5                 instr( ','||d||',' , ',' ,1,r)+1 ,
  6                 instr( ','||d||',' , ',' ,1,r+1) -
  7                 instr( ','||d||',' , ',' ,1,r) -1) txt
  8      from emp ,
  9         ( select level r
 10           from dual
 11           connect by level <= ( select max(did) from dept )
 12         )
 13  where r <= length(d) - length(replace(d,',')) + 1
 14  )
 15  select *
 16  from dept_as_rows
 17  order by 1,2
 18  /

       EID EN         TXT
---------- ---------- ----------------------------------------------------------------------------------------
         1 Tom        1
         1 Tom        2
         2 Mike       2
         2 Mike       3
         3 Sue        3

5 rows selected.

--
-- now we can join back to dept to get the names
--

SQL>
SQL> with dept_as_rows as
  2  (
  3  select eid,en,
  4         to_number(substr( ','||d||',' ,
  5                 instr( ','||d||',' , ',' ,1,r)+1 ,
  6                 instr( ','||d||',' , ',' ,1,r+1) -
  7                 instr( ','||d||',' , ',' ,1,r) -1)) txt
  8      from emp ,
  9         ( select level r
 10           from dual
 11           connect by level <= ( select max(did) from dept )
 12         )
 13  where r <= length(d) - length(replace(d,',')) + 1
 14  )
 15  select *
 16  from dept_as_rows dr,
 17       dept d
 18  where dr.txt = d.did
 19  order by 1,2
 20  /

       EID EN                TXT        DID DN
---------- ---------- ---------- ---------- --------------------
         1 Tom                 1          1 Sales
         1 Tom                 2          2 HR
         2 Mike                3          3 Testing
         2 Mike                2          2 HR
         3 Sue                 3          3 Testing

5 rows selected.

--
-- now we can concatenate using LISTAGG
--

SQL>
SQL>
SQL> with dept_as_rows as
  2  (
  3  select eid,en,
  4         to_number(substr( ','||d||',' ,
  5                 instr( ','||d||',' , ',' ,1,r)+1 ,
  6                 instr( ','||d||',' , ',' ,1,r+1) -
  7                 instr( ','||d||',' , ',' ,1,r) -1)) txt
  8      from emp ,
  9         ( select level r
 10           from dual
 11           connect by level <= ( select max(did) from dept )
 12         )
 13  where r <= length(d) - length(replace(d,',')) + 1
 14  )
 15  select dr.eid, dr.en, listagg(d.dn,',') within group ( order by d.did ) as depts
 16  from dept_as_rows dr,
 17       dept d
 18  where dr.txt = d.did
 19  group by dr.eid, dr.en
 20  order by 1,2
 21  /

       EID EN         DEPTS
---------- ---------- ------------------------------
         1 Tom        Sales,HR
         2 Mike       HR,Testing
         3 Sue        Testing

3 rows selected.

SQL>
SQL>
SQL>

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Amazing

Mohini Jadhav, August 23, 2017 - 4:58 am UTC

Thanks a lot!! This was so quick response. You saved my life.

Mohini Jadhav, August 23, 2017 - 5:48 am UTC

Hi ,

I am not sure where I am doing wrong in this, getting not a group by expression error error.

with platform_as_rows as (
select
ad.allocid,
to_number(substr( ','||ad.PLATFORMS||',' ,
instr( ','||ad.PLATFORMS||',' , ',' ,1,r)+1 ,
instr( ','||ad.PLATFORMS||',' , ',' ,1,r+1) -
instr( ','||ad.PLATFORMS||',' , ',' ,1,r) -1)) txt
from allocationdetails ad,
(select level r from dual connect by level <= (select max(pl.platformid) from platform_lookup pl))
where r <= length(ad.PLATFORMS) - length(replace(ad.PLATFORMS,',')) + 1
)
select
ALLOCATIONDETAILS.ALLOCID,
CUSTOMER.ORACLEID as ORACLEID,
CUSTOMER.CUSTOMERNAME as CUSTOMERNAME,
SERVICECATALOG.SERVICENAME as SERVICENAME,
SIZELOOKUP.SIZEABBR as SIZEABBR,
PHASELOOKUP.PHASENAME as PHASENAME,
ACE_EMPLOYEES.FNAME || ' ' || ACE_EMPLOYEES.LNAME as Employee,
( SELECT m.fname||' '|| m.lname as d FROM ACE_EMPLOYEES m WHERE m.ISSUPERVISOR = 'Yes' AND m.ENDDATE IS NULL AND m.SRID = ACE_EMPLOYEES.SUPERVISORID ) as SUPERVISOR,
ROLELOOKUP.ROLEABBR as ROLEABBR,
ALLOCATIONDETAILS.STARTDATE as STARTDATE,
ALLOCATIONDETAILS.ENDDATE as ENDDATE,
listagg(d.platformname,',') within group ( order by d.platformid ) as platformsList,
(ALLOCATIONDETAILS.WEEKLYALLOCPERCENT*100) as WEEKLYALLOCPERCENT
from
platform_as_rows pr, platform_lookup d, ALLOCATIONDETAILS ALLOCATIONDETAILS
LEFT JOIN PHASELOOKUP PHASELOOKUP ON(ALLOCATIONDETAILS.CUSTOMERSTAGEID=PHASELOOKUP.PHASEID)
LEFT JOIN SERVICECATALOG SERVICECATALOG ON (ALLOCATIONDETAILS.SERVICEID=SERVICECATALOG.SERVICEID)
LEFT JOIN ROLELOOKUP ROLELOOKUP ON (ALLOCATIONDETAILS.ROLEID = ROLELOOKUP.ROLEID)
LEFT JOIN SIZELOOKUP SIZELOOKUP ON (ALLOCATIONDETAILS.SIZEID=SIZELOOKUP.SIZEID)
LEFT JOIN CUSTOMER CUSTOMER ON (ALLOCATIONDETAILS.CUSTOMERID=CUSTOMER.CUSTOMERID)
LEFT JOIN ACE_EMPLOYEES ACE_EMPLOYEES ON (ALLOCATIONDETAILS.EMPLOYEEID=ACE_EMPLOYEES.SRID)
where pr.txt = d.platformid
group by pr.ALLOCID
order by 1

If I execute in without joins its working as expected.
with platform_as_rows as (
select
allocid,
to_number(substr( ','||PLATFORMS||',' ,
instr( ','||PLATFORMS||',' , ',' ,1,r)+1 ,
instr( ','||PLATFORMS||',' , ',' ,1,r+1) -
instr( ','||PLATFORMS||',' , ',' ,1,r) -1)) txt
from allocationdetails,
(select level r from dual connect by level <= (select max(platformid) from platform_lookup))
where r <= length(PLATFORMS) - length(replace(PLATFORMS,',')) + 1
)
select pr.allocid, listagg(d.platformname,',') within group ( order by d.platformid ) as platforms
from platform_as_rows pr,
platform_lookup d
where pr.txt = d.platformid
group by pr.allocid
order by 1

Kindly help me on this. I am sure, it may be very silly thing which I am doing.
Thanks in advance.

August 23, 2017 - 7:09 am UTC

In that case, just keep extending with the WITH statement to help diagnose things, ie,

with platform_as_rows as ( 
select 
allocid, 
to_number(substr( ','||PLATFORMS||',' , 
instr( ','||PLATFORMS||',' , ',' ,1,r)+1 , 
instr( ','||PLATFORMS||',' , ',' ,1,r+1) - 
instr( ','||PLATFORMS||',' , ',' ,1,r) -1)) txt 
from allocationdetails, 
(select level r from dual connect by level <= (select max(platformid) from platform_lookup)) 
where r <= length(PLATFORMS) - length(replace(PLATFORMS,',')) + 1 
),
interim_results as (
select pr.allocid, listagg(d.platformname,',') within group ( order by d.platformid ) as platforms 
from platform_as_rows pr, 
platform_lookup d 
where pr.txt = d.platformid 
group by pr.allocid 
)
select ...
from interim_results
left join phaselookup phaselookup on(allocationdetails.customerstageid=phaselookup.phaseid) 
left join servicecatalog servicecatalog on (allocationdetails.serviceid=servicecatalog.serviceid) 
left join rolelookup rolelookup on (allocationdetails.roleid = rolelookup.roleid) 
left join sizelookup sizelookup on (allocationdetails.sizeid=sizelookup.sizeid) 
left join customer customer on (allocationdetails.customerid=customer.customerid) 
left join ace_employees ace_employees on (allocationdetails.employeeid=ace_employees.srid)

or similar.

Mohini Jadhav, August 23, 2017 - 8:35 am UTC

Ohh!
with platform_as_rows as (
select
allocid,
to_number(substr( ','||PLATFORMS||',' ,
instr( ','||PLATFORMS||',' , ',' ,1,r)+1 ,
instr( ','||PLATFORMS||',' , ',' ,1,r+1) -
instr( ','||PLATFORMS||',' , ',' ,1,r) -1)) txt
from allocationdetails,
(select level r from dual connect by level <= (select max(platformid) from platform_lookup))
where r <= length(PLATFORMS) - length(replace(PLATFORMS,',')) + 1
),
interim_results as (
select pr.allocid, listagg(d.platformname,',') within group ( order by d.platformid ) as platforms
from platform_as_rows pr,
platform_lookup d
where pr.txt = d.platformid
group by pr.allocid
)
select allocationdetails.ALLOCID,
CUSTOMER.ORACLEID as ORACLEID,
CUSTOMER.CUSTOMERNAME as CUSTOMERNAME,
SERVICECATALOG.SERVICENAME as SERVICENAME,
SIZELOOKUP.SIZEABBR as SIZEABBR,
PHASELOOKUP.PHASENAME as PHASENAME,
ACE_EMPLOYEES.FNAME || ' ' || ACE_EMPLOYEES.LNAME as Employee,
( SELECT m.fname||' '|| m.lname as d FROM ACE_EMPLOYEES m WHERE m.ISSUPERVISOR = 'Yes' AND m.ENDDATE IS NULL AND m.SRID = ACE_EMPLOYEES.SUPERVISORID ) as SUPERVISOR,
ROLELOOKUP.ROLEABBR as ROLEABBR,
allocationdetails.STARTDATE as STARTDATE,
allocationdetails.ENDDATE as ENDDATE,
ir.platforms,
(allocationdetails.WEEKLYALLOCPERCENT*100) as WEEKLYALLOCPERCENT
from interim_results ir, allocationdetails allocationdetails
left join phaselookup phaselookup on(allocationdetails.customerstageid=phaselookup.phaseid)
left join servicecatalog servicecatalog on (allocationdetails.serviceid=servicecatalog.serviceid)
left join rolelookup rolelookup on (allocationdetails.roleid = rolelookup.roleid)
left join sizelookup sizelookup on (allocationdetails.sizeid=sizelookup.sizeid)
left join customer customer on (allocationdetails.customerid=customer.customerid)
left join ace_employees ace_employees on (allocationdetails.employeeid=ace_employees.srid)

When I am doing this I am getting only first set/last set of list in all columns

I am not getting where I am doing wrong.

Resolved

Mohini Jadhav, August 23, 2017 - 8:44 am UTC

Ohh Man!
I was making a very foolish mistake. Was not joining the with clause with a select query.

It's been resolved now.

Thank you so much!

Variant

Racer I., August 23, 2017 - 1:29 pm UTC

with
norm AS (
  select eid, en, ',' || REPLACE(d, ' ') || ',' d from emp),
allde AS (
  select n.*, d.*
  from norm n
    cross join dept d
  where n.d LIKE '%,' || d.did || ',%')
select eid, en, listagg(dn,',') within group (order by did) depts
from allde       
group by eid, en
order by 1,2

PS : bad design...

August 24, 2017 - 12:59 am UTC

PS : bad design...

Definitely !

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Amazing

Resolved

Variant

More to Explore

SQL

Alternative for GROUP_CONCAT() and FIND_IN_SET() in mysql for selecting values from a column with comma separated values.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Amazing

Resolved

Variant

More to Explore

SQL