SQL join, display multiple values into one row

Question

If I have the following tables (just an example):

table1:

id	tax year
1	2000
1	2001
2	2001

table2:

id	tax year	value
1	2000	a
1	2000	b
1	2000	c

How would I join the two tables so I can display multiple values into one row like this?

id	tax year	value
1	2000	a,b,c
1	2001	---
2	2001	---

I am using db2 so I'm not able to use some functions such as GROUP_CONCAT(). Would LIST_AGG() be an option? Or is there a way to have a nested select statement and somehow concatenate the results, something like this?

select
 id
,concat((select value
  from database.table2 tab2
  join database.table1 tab1
  on tab1.id=tab2.id))
from database.table1

Answer 1

You can use LISTAGG() as shown below:

select a.id, a.tax_year,
  listagg(b.value, ',') within group (order by b.value) as value
from table1 a
left join table2 b on b.id = a.id and b.tax_year = a.tax_year
group by a.id, a.tax_year;

Result:

 ID  TAX_YEAR  VALUE 
 --- --------- ----- 
 1   2000      a,b,c 
 1   2001      null  
 2   2001      null  

See running example at db<>fiddle.