WITH GroupedAttributes AS (
SELECT
Attribute_Name,
Attribute_sub_type,
SUM(CASE WHEN Attribute_sub_type IS NULL THEN 1 ELSE 0 END)
OVER (PARTITION BY Attribute_Name ORDER BY ROWNUM) AS GroupNum
FROM
attribute_sorting
WHERE
Attribute_sub_type IS NOT NULL
)
SELECT
Attribute_Name,
GroupNum,
LISTAGG(Attribute_sub_type, ', ') WITHIN GROUP (ORDER BY Attribute_sub_type) AS SubTypes
FROM
GroupedAttributes