I need to concatenate rows values into a column based on which group the row belongs to using two grouping values.
TBL1
cat1 cat2 cat3 value
---- ---- ---- -----
1 1 lvl1 100
1 2 lvl2 abc
1 3 lvl2 cba
2 1 lvl1 200
2 2 lvl2 abb
3 1 lvl1 100
3 2 lvl2 bbc
3 3 lvl2 acc
3 4 lvl1 400
3 5 lvl2 acc
4 1 lvl1 300
4 2 lvl2 aab
...
TBL2
cat1 cat2 value
---- ---- ---------
1 100 abc, cba
2 200 abb
3 100 bbc, aac
3 400 aac
4 300 aab
...
This is using static DB2 SQL. The actual table has over a thousand records.
At least some versions of DB2 support listagg(). So the tricky part is identifying the groups. You can do this by counting the number of rows with where the value is a number, cumulatively. The resulting query is something like this:
select cat1,
max(case when value >= '0' and value <= '999' then value end) as cat2,
listagg(case when not value >= '0' and value <= '999' then value end, ', ') within group (order by cat2) as value
from (select t.*,
sum(case when value >= '0' and value <= '999' then 1 else 0 end) over (order by cat1, cat2) as grp
from t
) t
group by cat1, grp;
Checking for a number in DB2 can be tricky. The above uses simple between logic that is sufficient for your sample data.