WITH tab AS
(
SELECT 1 as col1, 2 as col2, 'Smith' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, 'John' as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, 'Ajay' as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, 'Ram' as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, 'Jack' as created_by FROM dual
)
SELECT listagg(col2_with_nulls, ',') within
GROUP(
ORDER BY col2_with_nulls) col2_list
FROM (SELECT col1,
CASE
WHEN lag(col2) over(ORDER BY col2) = col2 THEN
NULL
ELSE
col2
END AS col2_with_nulls,
created_by
FROM tab);
(
SELECT 1 as col1, 2 as col2, 'Smith' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, 'John' as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, 'Ajay' as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, 'Ram' as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, 'Jack' as created_by FROM dual
)
SELECT listagg(col2_with_nulls, ',') within
GROUP(
ORDER BY col2_with_nulls) col2_list
FROM (SELECT col1,
CASE
WHEN lag(col2) over(ORDER BY col2) = col2 THEN
NULL
ELSE
col2
END AS col2_with_nulls,
created_by
FROM tab);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7580948/viewspace-1976005/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
考驾流程图
请登录后发表评论
登录
全部评论
