469,354 Members | 2,035 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,354 developers. It's quick & easy.

listagg and ignoring duplicates

185 128KB
Is there a way to tell listagg aggregate function to ignore the duplicates?

Here's the sample data I used in my test:
Expand|Select|Wrap|Line Numbers
  1. db2 "create table test (id int, name varchar(2))"
  2. db2 "insert into test values (1,'a')"
  3. db2 "insert into test values (1,'b')"
  4. db2 "insert into test values (1,'b')"
  5. db2 "insert into test values (2,'x')"
  6. db2 "insert into test values (2,'x')"
  7. db2 "insert into test values (2,'y')"
  8.  
The query syntax is:
Expand|Select|Wrap|Line Numbers
  1. db2 "select id, listagg(name, ',') within group (order by name) from test group by id"
The above query would return something like:
Expand|Select|Wrap|Line Numbers
  1. ID          2
  2. 1           a,b,b
  3. 2           x,x,y
  4.  
I would like it to return:
Expand|Select|Wrap|Line Numbers
  1. ID          2
  2. 1           a,b
  3. 2           x,y
  4.  
Any ideas?
Jan 6 '13 #1
4 21894
Rabbit
12,516 Expert Mod 8TB
I don't know if the list_agg function has an ignore duplicate option but you can run it against a distinct source so there's no duplicates to aggregate in the first place.

Expand|Select|Wrap|Line Numbers
  1. select list_agg(field, ',')
  2. from (
  3.    select distinct field
  4.    from table
  5. ) t
Jan 7 '13 #2
Anas Mosaad
185 128KB
Thanks Rabbit for your reply. I forgot to post the solution I got. Yours looks fine. Another solution -if you have many columns involved- would be to use OLAP functions as below:
Expand|Select|Wrap|Line Numbers
  1. db2 "select id, listagg(distinct name, ',') within group (order by name) 
  2. from 
  3.    (select id, name, row_number() over (partition by id, name order by id) as rnum 
  4.    from t) 
  5. where rnum = 1 group by id"
Your solution and mine works fine when listagg is the only aggregate function. However, combining it with more aggregate functions can be a bit tricky that you may think about. Anyway, this solves my problems already.
Jan 8 '13 #3
Here is an alternative solution that will work even in cases where several aggregation functions are reqired:

Expand|Select|Wrap|Line Numbers
  1. select id,
  2.        xmlquery ('distinct-values($x//row)' passing xmlgroup (name) as "x")
  3.   from test
  4. group by id
  5.  
The trick is to first aggregate the rows into an XML document, and then use the XPath 2.0 function distinct-values() to get rid of duplicates. The result list will be space separated initially, but it would not be hard to replace the spaces with commas in a separate step.
Aug 23 '13 #4
pp8771
1
Expand|Select|Wrap|Line Numbers
  1. select id,
  2.        xmlquery ('distinct-values($x//row)' passing xmlgroup (name) as "x")
  3.   from test
  4. group by id
How can insert a comma in this query to get this kind of output.
like to use xmlquery function in db2 database

Expand|Select|Wrap|Line Numbers
  1. ID          2
  2. 1           a,b
  3. 2           x,y
Apr 14 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by vsgdp | last post: by
9 posts views Thread by paul.jameson | last post: by
14 posts views Thread by ak | last post: by
3 posts views Thread by ryan.paquette | last post: by
Thekid
3 posts views Thread by Thekid | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.