I have a table where storing details
ID NAME
1 A
2 A
1 A
I need the output like
ID Name Count
1,2 A 3
Please help to get the output like that in oracle select query
Asked
Active
Viewed 53 times
1
a_horse_with_no_name
- 497,550
- 91
- 775
- 843
Sreejith A
- 27
- 7
-
Have you tried a query yet? Also, please tell us which version of Oracle you are using. – Tim Biegeleisen Mar 14 '18 at 11:03
-
1https://stackoverflow.com/questions/tagged/string-aggregation+oracle – a_horse_with_no_name Mar 14 '18 at 11:05
-
Oracle 11 we are using – Sreejith A Mar 14 '18 at 11:09
-
1Possible duplicate of [SQL Query to concatenate column values from multiple rows in Oracle](https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) – JohnLBevan Mar 14 '18 at 11:10
1 Answers
2
In Oracle, you can use listagg(), but it has no distinct option. So, use a subquery and two levels of aggregation:
select listagg(id, ',') within group (order by id) as id, name, sum(cnt)
from (select id, name, count(*) as cnt
from t
group by id, name
) x
group by name;
Gordon Linoff
- 1,198,228
- 53
- 572
- 709