Oracle Sql LISTAGG with CASE

Question

我正在尝试向下面的查询添加一个LISTAGG函数，以便输出可以是由commas.Can分隔的单个值。有人建议如何向其添加LISTAGG函数。

SELECT CASE A.ATTRIBUTE_NAME
WHEN 'EA' THEN 'EMAIL ADDRESS'
WHEN 'CP' THEN 'Company'
WHEN 'OG' THEN 'Organization'
END AS USER_DETAILS
FROM USERS A LEFT OUTER JOIN DETAILS B
ON A.ID = B.USER_ID
WHERE USER_NAME LIKE '%John%'

Answer 1

根据您的描述，您只需要将case表达式放在listagg()调用中：

SELECT LISTAGG(
  CASE A.ATTRIBUTE_NAME
    WHEN 'EA' THEN 'EMAIL ADDRESS'
    WHEN 'CP' THEN 'Company'
    WHEN 'OG' THEN 'Organization'
  END,
  ',') WITHIN GROUP (ORDER BY A.ATTRIBUTE_NAME) AS USER_DETAILS
FROM USERS A LEFT OUTER JOIN DETAILS B
ON A.ID = B.USER_ID
WHERE USER_NAME LIKE '%John%'

尽管它看起来应该引用B.ATTRIBUTE_NAME；但也许您在修改posting代码时弄错了。并且您可能希望对列表中的项进行不同的排序。