CODEWITHSUNDEEP
oracle-database
joe
joe

Reputation: 1473

Listagg syntax in Oracle 10, 11

I have a simple program:

Select 
Soccer||', ' ||football  as test,
ID
From sport
Where ID = 123

Here is the result from query above:

Test       ID
Adis, Nike 123
,          123

How do I edit my code below to remove the comma "," such that it only displays one row?

Select
LISTING (a.test, ', ') within group (order by a.test) as equipment,
ID
From
    (
     Select
     soccer||', '||football as test,
     ID
     From test
     where ID =123
    )a
Group by I'd

The result shows

Equipment       ID
, , Adis, Nike  123

My desired result:

Equipment  ID
Adis, Nike 123

I am running Oracle 11.

Upvotes: 0

Views: 209

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

Your current listagg is aggregating the existing concatenated results from the two rows, 'Adis, Nike' and ', ', as a list with another comma between them. The concatenation probably isn't what you really want; though you could make it skip the comma if there is only one value:

select soccer
  || case when soccer is not null and football is not null then ', ' end
  || football as test, id
from sport
where id = 123;

TEST                           ID
---------------------- ----------
Adis, Nike                    123
                              123

And then exclude any null values in your outer listagg:

select listagg (equipment, ', ') within group (order by equipment) as equipment, id
from (
  select soccer
    || case when soccer is not null and football is not null then ', ' end
    || football as equipment, id
  from sport
  where id = 123
)
where id = 123
and equipment is not null
group by id;

EQUIPMENT                              ID
------------------------------ ----------
Adis, Nike                            123

You could also use a union to convert the columns into separate rows (a kind of manual unpivot):

select id, soccer as equipment from sport
union all
select id, football as equipment from sport;

        ID EQUIPMENT                    
---------- ------------------------------
       123 Adis                          
       123                               
       123 Nike                          
       123

And then use that as an inline view, aggregating the list it returns, and excluding the null entries:

select listagg (equipment, ', ') within group (order by equipment) as equipment, id
from (
  select id, soccer as equipment from sport
  union all
  select id, football as equipment from sport
)
where id = 123
and equipment is not null
group by id;

EQUIPMENT                              ID
------------------------------ ----------
Adis, Nike                            123

Upvotes: 1

Related Questions