Need to combine one column's value from many rows into a single row

Question

I'm try to create a report on Teradata security (DBC.ALLRIGHTS). When a user has more than one permission, I would like to group those into one row.

These rows:

• DatabaseX TableX Dave A
• DatabaseX TableX Dave C
• DatabaseX TableX Dave D
• DatabaseX TableX Dave R

Would be combined like this:

• DatabaseX TableX Dave A,C,D,R

Answer 1

You can use XMLAgg for this:

select
databasename,
tablename,
username,
TRIM(TRAILING ',' FROM (XMLAGG(TRIM(AccessRight)|| ',' ORDER BY AccessRight) 
 (VARCHAR(10000))))
from
dbc.allrights
group by 1,2,3