1

I have a column in my SQLite database table (fldSuit) which stores the codes of a list of limitations in a single string. These codes each have a description in a separate table (fldAtts). Example below:

fldsSuit
fld_id    fld_limits
1         HR, HST

fldAtts
fld_att_code     fld_att_desc
HCA              Hindrance - Canals
HR               Hindrance - Row Width
HST              Hindrance - Stones

How do i query the database to look like:

Desired Output
Field     Limitations
1         Hindrance - Row Width, Hindrance - Stones

Hope you can help me.

EDIT: Put the actual names above and tried the solution by Tim below:

SELECT
    f.fld_id, 
    group_concat(a.fld_att_desc) as Limitations
FROM fldsSuit f
LEFT JOIN fldAtts a
    ON ',' || REPLACE(Limitations, ' ', '') || ',' LIKE
    '%,' || a.fld_att_code || ',%'
GROUP BY
    f.fld_id;
William Ernesto
  • 11
  • 2
  • Splitting strings in SQLite requires the use of an ugly (and inefficient-looking) CTE, see here: https://stackoverflow.com/a/32051164/159145 - I think you should perform the aggregation of `Limitation` values in your application code, then issue a second query to get the desired results. – Dai Feb 06 '18 at 03:27
  • @Dai We don't need a CTE to solve this query. – Tim Biegeleisen Feb 06 '18 at 03:36

1 Answers1

0

SQLite has a GROUP_CONCAT function which can help here. We can aggregate by field, and then take the group concatenation of the value. The join condition is messy, because we need to match individual codes from the second table against the CSV list of limitations in the first table.

SELECT
    t1.Field,
    GROUP_CONCAT(t2.Value) AS Limitations
FROM Table1 t1
LEFT JOIN Table2 t2
    ON ',' || REPLACE(t1.Limitations, ' ', '') || ',' LIKE
           '%,' || t2.Code || ',%'
GROUP BY
    t1.Field;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360