DB2 Aggregate Function

问题:

I will like to create a view in DB2 with group by function. Not sure whether SQL statement able to create such view.

Data:

TableId   department    item
1         dept1         item1
2         dept2         item1
3         dept1         item1
4         dept2         item1

View output:

department    item     id
dept1         item1    1,3
dept2         item1    2,4

I able to create a view to group by department and item, but unsure on how to combine the table in the view.

Seek for advise.

Many Thanks in advance.

回答1:

You can do that with LISTAGG function. Please take a look at this article to know how to use it: https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/listagg?lang=en

select department, listagg(TableId, ',') within group (order by TableId) as id
from YourTable
group by department; 

For more information: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0058709.html

You can also use XMLCast + XMLGroup: https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/aggregating_strings42?lang=en