I have a table like this

Trying to get the output as below

The below SQL will do the concatenation of ID2 values for the same ID1. How do I get the count of ID1 based on the number of combinations of ID1,ID2 also?

SELECT
ID1,
LISTAGG(ID2, ', ')
WITHIN GROUP (ORDER BY ID2) "ID2Values"
FROM table_name

CodePudding user response：

SELECT ID1,
     LISTAGG(ID2, ', ') WITHIN GROUP (ORDER BY ID2) "ID2Values",
     count(*)
FROM table_name
group by ID1