Oracle Sql select all values in a row without using aggregate functions and procedures

Question

I have this code:

select eto.id,( epe.surname || ' ' || epe.name  || ' ' || epe.patronymic) fio
                        from employee_trip_orders eto
                        left join employee_card_order_links ecol
                        on eto.id = ecol.trip_order_id
                        left join employee_trip_cards etc
                        on ecol.trip_card_id = etc.employee_trip_card_id 
                        left join eip_persons.employees epe
                        on etc.employee_id = epe.employee_id
                        where eto.id=2223
                        order by eto.id

and result of executing this code

id     fio
------------
2223 John Smith Brown
2223 Jack Orange Apple
2223 Jordan Stinky Tomato

I would like to transform this into single row with comma separators

id     fio
-------------------------------
2223 |  John Smith Brown,
     |  Jack Orange Apple,
     |  Jordan Stinky Tomato
-------------------------------

How can i achieve this without using external functions, group by and agregate functions? Thanks

No-no, @a_horse. "... without using external functions, group by and agregate functions". If I were you (the OP), I'd write it down on a piece of paper, putting comma wherever appropriate. That approach doesn't use external functions, etc. — Littlefoot, Mar 06 '20 at 10:51
@Littlefoot: aggregating without aggregate functions seems a rather futile attempt — , Mar 06 '20 at 10:53
@a_horse ("... seems a rather futile attempt"). Of course it does. Goes into "how to make simple things complex". — Littlefoot, Mar 06 '20 at 11:03
Why do you need to do this without external functions, group by and aggregate functions? Aggregation is exactly what you need here. It's a bit like saying "how can I play the piano without touching the keys?" ... you're making things unnaturally hard for yourself by constraining yourself like this. What's your reason? — Boneist, Mar 06 '20 at 11:17
Sorry everyone, i formulate my question wrong, i just need a concatenate strings in one field without group by. I just try to use listagg like this select auid, listagg(title, ', ') from study_group but, how can i do without group by? — Vytsalo, Mar 06 '20 at 17:05

score 1 · Accepted Answer · answered Mar 06 '20 at 11:00

You can do it using connect by query, but it is slower and more complicated than simple listagg().

select id, ltrim(sys_connect_by_path(fio, ', '), ', ') names
  from (select row_number() over (partition by id order by fio) rn, id, fio from t)
  where connect_by_isleaf = 1
  connect by id = prior id and rn = prior rn + 1
  start with rn = 1

^dbfiddle

Oracle Sql select all values in a row without using aggregate functions and procedures

1 Answers1