Separator in group_concat

[DSMejantel]

Hello !
I tried to write a list with multiples datas with the foldable component.
I use a group_concat.
How can I return to the next line instead to have a ',' as separator.
I've tried '/n' or '/r' or char(10) or char(13). But no change on my component...

[lyderic]

Hi,

If you want to escape characters, you have to use backslashes i.e. '\n' or '\r' and not '/n', '/r'...

Otherwise, something like that works for me with SQLite (3.46):

SELECT GROUP_CONCAT(foo, CHAR(10));

Which database are you using?

[DSMejantel]

Thanks,
I used \n' or '\r' (I wrote a mistake in the message).
I use sqlite as database

When I write :
group_concat(horodatage||' : '||nature||' --> '||notes , CHAR(10)) as description

[lovasoa]

Hello !

You need to use markdown if you want to create multiple paragraphs of text.
In markdown, you can create a new paragraph just by leaving a blank line between two chunks of text (so, you need two line break characters: one to terminate the line, and one to create a new blank line).

The following will output a b c d as a single paragraph:

select 
    'foldable' as component;
select 
    'The foldable component' as title,
    '
a

b

c

d' as description;

but the following will create multiple paragraphs, the way you want:

select 
    'foldable' as component;
select 
    'The foldable component' as title,
    '
a

b

c

d' as description_md;

(description_md instead of description)

[lovasoa]

So, in your case, with group_concat, the solution would look like this:

select 
    'foldable' as component;
select 
    title,
     group_concat(
        horodatage||' : '||nature||' --> '||notes ,
        CHAR(10) || CHAR(10) -- two line breaks between paragraphs
    ) as description_md;

[DSMejantel]

Thanks,
It's ok for me with this soluce.
And thanks for this new component !

[DSMejantel]

Hi,
I try to use this in a group_concat(distinct X || CHAR(10) || CHAR(10)) with sqlite.
It's seem impossible...
Do you have some soluces for this too ?

[lovasoa]

This should work... Do you have an example that I could easily reproduce?

[DSMejantel]

-- Fiches
select 
    'columns' as component WHERE $tab=1;
select 
    --(SELECT pole from poles WHERE poles.id=pole_id)    as title,
    'tools' as icon,
    group_concat(distinct nom,
        CHAR(10) || CHAR(10)) as description_md,
    json_group_array(json_object('icon','tools','color','red','description',categorie)) as item,
    entreprise  as button_text
    from inscription LEFT JOIN besoins on inscription.id=besoins.inscription_id LEFT JOIN services on services.id=besoins.categorie_id JOIN intervenants on inscription.id=intervenants.inscription_id WHERE $tab=1 GROUP BY inscription.id;

I have this message :

You can see what i want to build at https://forum.college-bourrillon.fr/besoins.sql

[lovasoa]

Oh, I see. the problem comes from group concat not allowing the use of both DISTINCT and a custom separator. You can replace

group_concat(distinct nom, CHAR(10) || CHAR(10)) 

with a subquery that does the de duplication separately. Something like

SELECT 
    (SELECT pole FROM poles WHERE poles.id = pole_id) as title,
    'tools' as icon,
    (SELECT group_concat(nom, CHAR(10) || CHAR(10))
     FROM (
         SELECT DISTINCT nom
         FROM intervenants 
         WHERE intervenants.inscription_id = inscription.id
         ORDER BY nom
     )) as description_md,
    json_group_array(json_object(
        'icon', 'tools',
        'color', 'red',
        'description', categorie
    )) as item,
    entreprise as button_text
FROM inscription 
LEFT JOIN besoins ON inscription.id = besoins.inscription_id 
LEFT JOIN services ON services.id = besoins.categorie_id 
WHERE $tab = 1 
GROUP BY inscription.id

[DSMejantel]

Thanks, It's OK now !