Search code examples
sqlsqliteselectgroup-concat

Sqlite group_concat select with "special needs"

I know how to use group_concat with Sqlite, to do the following:

id - f1 - f2 - f3
 1 -  1 -  a - NULL
 2 -  1 -  b - NULL
 3 -  2 -  c - NULL
 4 -  2 -  d - NULL

select id, f1, group_concat(f2), f3 from table group by f1

 result:
 2 -  1 - a,b - NULL
 4 -  2 - c,d - NULL

as you can see, the ID's 1 and 3 are dropped, which is the expected behaviour. But I would need:

 1 -  1 -  a - a,b
 2 -  1 -  b - a,b
 3 -  2 -  c - c,d
 4 -  2 -  d - c,d

so, every record returned, and another field (f3) updated with the group_concat

any idea how this could be done in Sqlite?

Thank you

Solution

  • Not sure WHY you want this, but here goes:

    select 
  outer_t.id
 ,outer_t.f1
 ,outer_t.f2
 ,inline_view.groupfoo
 from t as outer_t 
 left join (
  select 
      f1
     ,group_concat(f2) as groupfoo 
    from t 
    group by f1
 ) inline_view on inline_view.f1 = outer_t.f1
;