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 ;