How to use Oracle's LISTAGG function with a multi values?

user_10284006 0
Viewed 288

I have an 'ITEMS' table like below:

ITEM_NO     ITEM_NAME   
1               Book   
2               Pen   
3               Sticky Notes   
4               Ink   
5               Corrector   
6               Ruler

In another 'EMP_ITEMS' table I have the below:

EMPLOYEE        ITEMS_LIST   
 John           1,2   
 Mikel          5   
 Sophia         2,3,6  
 William        3,4   
 Daniel         null   
 Michael        6

The output has to be like this:

EMPLOYEE        ITEMS_LIST      ITEM_NAME   
John            1,2             Book,Pen   
Mikel           5               Corrector   
Sophia          2,3,6           Pen,Sticky Notes,Ruler   
William         3,4             Sticky Notes,Ink   
Daniel          null            null   
Michael         6               Ruler

I used the below query:

SELECT e.EMPLOYEE,e.ITEMS_LIST, LISTAGG(i.ITEM_NAME, ',') WITHIN GROUP (ORDER BY i.ITEM_NAME) ITEM_DESC 
 FROM EMP_ITEMS e  
INNER JOIN ITEMS i ON i.ITEM_NO = e.ITEMS_LIST 
GROUP BY e.EMPLOYEE,e.ITEMS_LIST;

But there is an error:

ORA-01722: invalid number

listagg select oracle
1 Answers

But there is an error: ORA-01722: invalid number

That is because your ITEMS_LIST is a string composed of numeric and comma characters and is not actually a list of numbers and you are trying to compare a single item number to a list of items.

Instead treat it as a string a look for sub-string matches. To do this you will need to surround the strings in the delimiter character and compare to see if one is the substring of the other:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Items ( ITEM_NO, ITEM_NAME ) As
SELECT 1, 'Book' FROM DUAL UNION ALL
SELECT 2, 'Pen' FROM DUAL UNION ALL
SELECT 3, 'Sticky Notes' FROM DUAL UNION ALL
SELECT 4, 'Ink' FROM DUAL UNION ALL
SELECT 5, 'Corrector' FROM DUAL UNION ALL
SELECT 6, 'Ruler' FROM DUAL;

CREATE TABLE emp_items ( EMPLOYEE, ITEMS_LIST ) AS
SELECT 'John', '1,2' FROM DUAL UNION ALL
SELECT 'Mikel', '5' FROM DUAL UNION ALL
SELECT 'Sophia', '3,2,6' FROM DUAL UNION ALL
SELECT 'William', '3,4' FROM DUAL UNION ALL
SELECT 'Daniel', null FROM DUAL UNION ALL
SELECT 'Michael', '6' FROM DUAL;

Query 1:

SELECT e.employee,
       e.items_list,
       LISTAGG( i.item_name, ',' )
         WITHIN GROUP (
           ORDER BY INSTR( ','||e.items_list||',', ','||i.item_no||',' )
         ) AS item_names
FROM   emp_items e
       LEFT OUTER JOIN
       items i
       ON ( ','||e.items_list||',' LIKE '%,'||i.item_no||',%' )
GROUP BY e.employee, e.items_list

Results:

| EMPLOYEE | ITEMS_LIST |             ITEM_NAMES |
|----------|------------|------------------------|
|     John |        1,2 |               Book,Pen |
|    Mikel |          5 |              Corrector |
|   Daniel |     (null) |                 (null) |
|   Sophia |      3,2,6 | Sticky Notes,Pen,Ruler |
|  Michael |          6 |                  Ruler |
|  William |        3,4 |       Sticky Notes,Ink |
user_15092640
Related