Oracle aggregation functions and how to concatenate all values ​​in the column

advertisements

All,

My Oracle Database is version 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

I have the following statement which usefully gets me the max (or min or count etc) values in each case as expected however what I would like is to get and to concatenate all of the values rather than the max, min or count - is there an elegant way to do this please ?

SELECT lla.id,
       max(decode(lla.attrid, 2, lla.valstr, null)) "Attribute Name 2",
       min(decode(lla.attrid, 3, lla.valstr, null)) "Attribute Name 3",
       count(decode(lla2.attrid, 5, lla2.valstr, null)) "Attribute Name 5"
FROM llattrdata lla, llattrdata lla2
WHERE lla.id = lla2.id
      AND lla.defid = 111111 --category id 1
      AND lla2.defid = 222222 --category id 2
      AND lla.id = 48212327 and lla2.id = 48212327
      GROUP BY lla.id

Hoping for a row that looks something like this:

12121212 | fred, jack, gill | 56 | 29,10

To be clearer it is the values that the 'Attribute Name 3' (for example) contains that I want to see all of and not just the max or the minimum. In other words for that attribute I can get the max or the min value or even the count but cannot see a way to get all of the values ? In other words I can get 10 as the min and 29 as the max - even 2 as the count but not 29 and 10 in the same column !

Many thanks in advance,


SELECT e.department_id,
       listagg(e.first_name) within group (order by e.department_id) "Attribute Name 2"
FROM employees e join
     departments d
     on e.department_id = d.department_id
GROUP BY e.department_id;

you can use above example and alter your query