Q: oracle listagg with name/value columns I have table "foo" like the following:
user_id attr_name    attr_value
-------------------------------
joe     user_id      abc123
joe     permission   A
joe     permission   B
joe     permission   C

I want the following result:
user_id       permission_List
-----------------------------
abc123        A, B, C

I can get the desired result with the following fugly query:
select 
user_id,
LISTAGG(permissions,', ') within group(order by permissions) AS permission_list
from
(
    SELECT a1.attr_value AS user_id, 
    a2.attr_value AS permissions
    from foo a1 
    JOIN foo a2 on a1.user_id=a2.user_id
    WHERE a1.attr_name='user_id'
       and a2.attr_name='permission'
       and a1.user_id='joe'
)
group by user_id;

There must be a cleaner way! Any suggestions?

A: Alas, the PIVOT operator doesn't work with LISTAGG, so you still need to use the old way of pivoting (conditional aggregation). But you can simplify the query, as shown below.
NOTE: In your sample data you have the first column named USER_ID, with the value 'john' - but then you have an attribute named USER_ID, with value 'abc123'. That makes no sense. I used a column name of NEW_USER_ID in my output, but I hope in real life you don't have to put up with such nonsense.
The WITH clause you see below is not part of the solution; I included it only for testing. Remove it, and in the remaining query use your actual table and column names everywhere.
with
  inputs ( user_id, attr_name, attr_value ) as (
    select 'joe', 'user_id'   , 'abc123' from dual union all
    select 'joe', 'permission', 'A'      from dual union all
    select 'joe', 'permission', 'B'      from dual union all
    select 'joe', 'permission', 'C'      from dual
  )
select   user_id,
         min(case attr_name when 'user_id' then attr_value end)      as new_user_id,
         listagg(case attr_name when 'permission' then attr_value end, ', ')
                                within group (order by attr_value)   as permission_list
from     inputs
where    attr_name in ('user_id', 'permission')
  and    user_id = 'joe'  --  if needed
group by user_id
;

USER_ID      NEW_USER_ID  PERMISSION_LIST   
------------ ------------ ------------------
joe          abc123       A, B, C          

