Get all ids of GROUP_CONCAT in SQLite

user_10932097 0
Viewed 25

Table B

id   name     tablename
1    abc      table1    
2    xyz      table2
3    abc      table1
4    sdf      table2
5    dfg      table1

Query:

SELECT B.tablename, GROUP_CONCAT(B.id),GROUP_CONCAT(B.name||'-'||cnt)
FROM (
    SELECT tablename, id, name, COUNT(*) cnt
    FROM B
    GROUP BY tablename,name
) B GROUP BY B.tablename

Output:

| tablename | GROUP_CONCAT(B.id) | GROUP_CONCAT(B.nameB||'-'||cnt) |
| ------ | ------------------ | ------------------------------- |
| table1 | 1,5                | abc-2,dfg-1                     |
| table2 | 2,4                | xyz-1,sdf-1                     |

But here i'm not getting full ids as you can see in output of table 1.

i want something like :

| tablename | GROUP_CONCAT(B.id) | GROUP_CONCAT(B.nameB||'-'||cnt) |
| ------ | ------------------ | ------------------------------- |
| table1 | 1,3,5              | abc-2,df-1                      |
| table2 | 2,4                | xyz-1,sdf-1                     |

Pls help me fix this.

group-concat sqlite
1 Answers

You should use GROUP_CONCAT() for the ids in the subquery also:

SELECT tablename, GROUP_CONCAT(id), GROUP_CONCAT(name||'-'||cnt)
FROM (
    SELECT tablename, GROUP_CONCAT(id) id, name, COUNT(*) cnt
    FROM B
    GROUP BY tablename, name
) 
GROUP BY tablename

See the demo.

user_104988280
Related