List enumeration in SQL?

Question

Say, there are 2 tables:

Person
| id | name |

LanguagesSpoken
| person_id | language_name |

Is there a way to create a view that would contain 2 columns: person_name and languages_spoken as a comma-separated list?

I'm developing against SQLite.

Answer 1

select name, group_concat(language_name) as languages
from person p inner join languagesSpoken l
on p.id = l.person_id
group by l.person.id

Answer 2

Use:

  SELECT p.name,
         GROUP_CONCAT(ls.language_name, ',')
    FROM PERSON p
    JOIN LANGUAGESSPOKEN ls ON ls.personid = p.personid
GROUP BY p.name

Reference: SQLite aggregate functions

Answer 3

group_concat() is your answer.