Menu
I have a table like this:
group_id name -------- ---- 1 David 1 John 1 Alan 1 David 2 Julie 2 Charles And I want the following result:
group_id names -------- ----- 1 'Alan, David, John' 2 'Charles, Julie' I can use the following query:
select group_id, listagg(name, ',') within group (order by name) as names from demotable group by group_id To get this (very similar result):
group_id names -------- ----- 1 'Alan, David, David, John' 2 'Charles, Julie' Any ideas how I can filter the names by uniqueness in the LISTAGG call?
387
With the DISTINCT option, the processing to remove duplicate values can be done directly within the LISTAGG function. The result is simpler, faster, more efficient SQL.
The code is like this :- SELECT col1 ,LISTAGG(col2, ',') within group (order by col2) FROM table T WHERE.... So, it shoukd show all the distinct values of col2 corresponding to col1 , seperated by comma.
An Oracle LISTAGG Function is an aggregate function that returns a single row. This is used to transform data from multiple rows into a single list of values separated by a given delimiter. It operates on all rows and returns single. It returns a comma or other delimiter separatedresult set just like an excel CSV file.
In order to concatenate field values, I would use “GROUP_CONCAT” function in Virtual DataPort Administration tool which is similar to LISTAGG function. For example, GROUP_CONCAT('<row separator>',<field_name>)
I don't have an 11g instance available today but could you not use:
SELECT group_id, LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS names FROM ( SELECT UNIQUE group_id, name FROM demotable ) GROUP BY group_id If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
