Add prefix with distinct in group_concat sql

Question

Hey I have this query

select group_concat(distinct(accountId) separator "' ,") 
from xxx_yyy_replacement_instances
where deletedAt IS NULL

which returns me the next results

act-fdf4caf8' ,act-525d62af' ,act-e141e6ab' ,act-73995962' ,act-c782e06d' ,act-993aaec0' ,act-1a2f883e' ,act-ed6f5f9e

I want add "'" before each accountId

I used concat inside but then it can't get the distinct(accountId), I used also

select group_concat(",",distinct(accountId) separator "' ,") 
from gcp_gce_replacement_instances
where deletedAt IS NULL

But I get an error with it.

The desired result is :'act-fdf4caf8','act-525d62af','act-e141e6ab','act-73995962','act-c782e06d','act-993aaec0','act-1a2f883e','act-ed6f5f9e'

`CONCAT("'", GROUP_CONCAT(DISTINCT column SEPARATOR "','"), "'")` — Akina, Jun 21 '22 at 08:02

score 1 · Answer 1 · answered Jun 21 '22 at 08:31

Don't put the second ' in the SEPARATOR, concatenate both of them with the account ID.

You don't need to call CONCAT() explicitly. You can put multiple values in the GROUP_CONCAT() arguments and they'll be concatenated.

SELECT GROUP_CONCAT(DISTINCT "'", accountId, "'" SEPARATOR ', ')
FROM gcp_gce_replacement_instances
WHERE deletedAt IS NULL

1 Answers1