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