Combine column from multiple rows into one row in SQLITE

Question

I have a sqlite-database with the schema

Articles
 Id
 Name

Tags
 Id
 Name

ArticlesTags
 Id
 ArticleId
 TagId

I´m trying to query for Articles.Name and a LIST of tags. Basically what I would want is:

Name1, (tag1,tag2,tag3)
Name2, (tag3,tag2)

Is this possible? I´m lacking the language to explain what I actually want to do, what is it called?

I found Combine column from multiple rows into single row but it seems limited to SQLServer and the usage of builtin xml-features of that.

Answer 1

You can use the GROUP_CONCAT() function:

SELECT a.Name               AS ArticleName
     , GROUP_CONCAT(t.Name) AS TagList
FROM Articles AS a
  LEFT JOIN ArticlesTags AS at
    ON at.ArticleId = a.Id
  LEFT JOIN Tags AS t
    ON t.Id = at.TagId
GROUP BY a.Id, a.Name ;