Teradata中XMLAGG查询结果出现重复值的解决求助

The duplicate AccessRight values you're seeing stem from your underlying join producing multiple rows for the same RoleName/DatabaseName/AccessRight combination. Teradata's XMLAGG doesn’t support DISTINCT directly inside the function, so we need to eliminate duplicates before aggregating.

Here's the modified query that uses a subquery to get distinct access rights first, then applies XMLAGG to the deduplicated dataset:

SELECT 
  RoleName, 
  DatabaseName,
  Trim(Trailing ',' FROM (XmlAgg(Trim(AccessRight) || ',' ORDER BY AccessRight) (VARCHAR(10000)))) AS AccessRight
FROM (
  -- Get unique role/database/access right combinations first
  SELECT DISTINCT
    A.RoleName, 
    B.DatabaseName, 
    B.AccessRight
  FROM DBC.RoleMembers A 
  JOIN DBC.AllRoleRights B ON A.RoleName = B.RoleName 
  WHERE Grantee='?your_id'
) AS DistinctRoleRights
GROUP BY 1, 2 
ORDER BY 2, 1, 3;

Why this works: #

1. The inner subquery uses DISTINCT to remove redundant rows where the same role, database, and access right appear multiple times from the join.
2. The outer query groups by RoleName and DatabaseName, then uses XMLAGG to concatenate only unique AccessRight values into a clean, comma-separated string.

If you prefer Teradata's GROUP BY syntax for deduplication (sometimes more performant), you can replace SELECT DISTINCT with a group by on all three columns in the subquery:

SELECT 
  RoleName, 
  DatabaseName,
  Trim(Trailing ',' FROM (XmlAgg(Trim(AccessRight) || ',' ORDER BY AccessRight) (VARCHAR(10000)))) AS AccessRight
FROM (
  SELECT
    A.RoleName, 
    B.DatabaseName, 
    B.AccessRight
  FROM DBC.RoleMembers A 
  JOIN DBC.AllRoleRights B ON A.RoleName = B.RoleName 
  WHERE Grantee='?your_id'
  GROUP BY 1, 2, 3
) AS DistinctRoleRights
GROUP BY 1, 2 
ORDER BY 2, 1, 3;

Either version will resolve duplicates like R, R, R, R to just R in your final results.

内容的提问来源于stack exchange，提问作者Marcucciboy2