SQLite Forum

group_concat with distinct and custom seperator
Login

group_concat with distinct and custom seperator

(1) By Cieric on 2021-04-09 23:54:06 [link] [source]

I'm going to keep this as brief as possible, I tried to run a command with something similar to group_concat(DISTINCT column1, '|') and received the following error

Result: DISTINCT aggregates must have exactly one argument
At line 1:
select group_concat(DISTINCT column1, '|') from example_table
group by column2;
Is there a way to do this without sub-queries? I would like to keep my query as simple as possible as someone else is going to have to maintain it and it already has multiple joins and sub queries.

Any and all help is very much appreciated.

(2) By Keith Medcalf (kmedcalf) on 2021-04-10 01:30:22 in reply to 1 [link] [source]

The parser presently does not recognize that the DISTINCT prefix may be used around a single argument to an aggregate/window function and parses DISTINCT as being followed by a list, so the comma is parsed as an indicator of a multiple element list, not as an indicator of the end of the DISTINCT list and the beginning of the next function argument.

You would have to rephrase the query to use a subquery:

select group_concat(column1, '|') from (select distinct column1 from example_table group by column2);

(3) By Cieric on 2021-04-16 19:54:19 in reply to 2 [link] [source]

Ah okay, thanks for the info. I was afraid that might have to be the solution. Thank you for the help.

(4) By Luc Daigle (elgiad007) on 2021-06-18 14:38:32 in reply to 1 [source]

Really not sure how efficient this is, but you could use the REPLACE function:

select replace(group_concat(DISTINCT column1), ',', ' ') from example_table

(5) By Makina (charles.buffet) on 2021-11-07 02:31:01 in reply to 1 [link] [source]

In my case I had some , in my fields and I could not use a sub query 
It took me 15 minutes but I found a solution
I made a mix adding pipes and my own separator

I know you posted allready a few months ago but in your case you can do this for example

select rtrim(replace(group_concat(DISTINCT column1||'@!'), '@!,', '|'),'@!') from example_table

(6.1) Originally by anonymous with edits by Stephan Beal (stephan) on 2023-08-06 11:14:02 from 6.0 in reply to 5 [link] [source]

thank you this worked! a simpler/faster solution would be to do something like:

GROUP_CONCAT(DISTINCT column1||'~')

and then the separator would effectively become ',~' so it would be easier to split values that have a comma

((edited by admin to fix markdown formatting.))