SQLITE: merging rows into single row if they share a column

Use the aggregate function group_concat(X) for that:

SELECT (a.LastName || " " || a.FirstName) AS AttendeeName     , a.PhotoURI     , group_concat(c.CompanyName) AS Companies     , group_concat(c.CompanyId)   AS CompanyIdsFROM   Attendee AS aJOIN   CompanyAttendeeRelation AS ca ON ca.AttendeeId = a.AttendeeIdJOIN   Company                 AS c  ON c.CompanyId = ca.CompanyIdGROUP  BY a.LastName, a.Firstname, a.PhotoURI;

(Using table aliases to make it shorter and easier to read.)

NULL values are excluded from the result. The manual:

the concatenation of all non-NULL values

The order of elements in CompanyIds and Companies is arbitrary, according to the manual:

The order of the concatenated elements is arbitrary.

Also note that "arbitrary" is not the same as "random". group_concat, like other aggregate functions, processes the set of rows in the order received. Without any ORDER BY, that order is dictated by whatever query plan is executed. There is no natural order in tables of relational databases (you cannot rely on insert order at all). But both instances of group_concat() in the same SELECT list process rows in the same order so that the 1st ID in CompanyIds corresponds to the 1st name in Companies.

You can impose your order with ORDER BY in a subquery. It's an implementation detail, but it's highly unlikely to change. Like:

SELECT (LastName || " " || FirstName) AS AttendeeName     , PhotoURI     , group_concat(CompanyName) AS Companies     , group_concat(CompanyId)   AS CompanyIdsFROM  (   SELECT a.LastName, a.FirstName, a.PhotoURI, c.CompanyName, c.CompanyId   FROM   Attendee AS a   JOIN   CompanyAttendeeRelation AS ca ON ca.AttendeeId = a.AttendeeId   JOIN   Company                 AS c  ON c.CompanyId = ca.CompanyId   ORDER  BY 1,2,3,4,5  -- or whatever you need   ) AS subGROUP  BY LastName, Firstname, PhotoURI;

The manual about the (optional) ordinal numbers in ORDER BY:

If the ORDER BY expression is a constant integer K then the expression is considered an alias for the K-th column of the result set (columns are numbered from left to right starting with 1).

Use the GROUP BY list as leading ORDER BY expressions for best results.

Don't do anything with the derived table after ordering that might rearrange it (like joining the subquery to another table etc.)

Finally, note that similar aggregate functions in other RDBMS can behave slightly differently. Related:

• Concatenate multiple result rows of one column into one, group by another column
• GROUP_CONCAT ORDER BY

The answer from this post will help you turn

Name     | company---------+----------Doe John | company ADoe John | company B

into

Name     | company-1 | company-2---------+-----------+----------Doe John | company A | company B

I'm thinking a inner select might help, like:

CREATE View AttendeeTableView ASSELECT  (LastName || " " || FirstName) as AttendeeName,  (  select CompanyNameFROM    Attendee A_innner JOIN    CompanyAttendeeRelation CAR  /* is this where company name is? */  ON      on CAR.AttendeeId = A.AttendeeId /* if not remove the joins and CAR */WHERE   A_inner.last_name = A_outer.last_name and        A_inner.first_name = A_outer.first_name),PhotoURI,CAR.CompanyId,CAR.AttendeeId FROM    Attendee A_outer JOIN    CompanyAttendeeRelation CAR_outer  ON      on CAR_outer.AttendeeId = A_outer.AttendeeId GROUP by LastName,FirstNameORDER BY LastName, FirstName;