SQLITE: concatenate rows into one row if they split columns

Question

From the previous post, I have the following view in sqlite3:

CREATE View AttendeeTableView AS SELECT (LastName || " " || FirstName) as AttendeeName, CompanyName, PhotoURI, CompanyAttendeeRelation.CompanyId, CompanyAttendeeRelation.AttendeeId FROM Attendee JOIN CompanyAttendeeRelation on CompanyAttendeeRelation.AttendeeId = Attendee.AttendeeId ORDER BY LastName; 

Now, since the data is generated from the many-to-many relationship between Attendee and Company , I can get results such as:

 Doe John | company A | johnPic.png | 1 | 15 Doe John | company B | johnPic.png | 2 | 15 

What would I like to do in cases where several companies (for example, above) create a query that displays:

 Doe John | company A company B | johnPic.png | 1 2 | 15 

And one more conclusion:

 Doe John | company A | company B | johnPic.png | 1 | 2 | 15 

Therefore, I need to know how to merge a specific column for rows that have different values ​​in this table.

Any ideas?

Just in case, company A company B in the first request is obviously a concatenation of text, that is, something like strings (row1.CompanyName || " " || row2.CompanyName)

Answer 1

Now I understand what you mean. To do this, use the aggregate function group_concat(X) .
More details in the manual .

 SELECT (a.LastName || " " || a.FirstName) AS AttendeeName , a.PhotoURI , group_concat(c.CompanyName) AS CompanyIds , group_concat(c.CompanyId) AS Companies FROM Attendee AS a JOIN CompanyAttendeeRelation AS ca ON ca.AttendeeId = a.AttendeeId JOIN Company AS c ON ca.CompanyId = c.CompanyId; GROUP BY a.LastName, a.Firstname, a.PhotoURI 

Also use table aliases to make them shorter and easier to read.

Answer 2

The response of this message will help you turn

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

in

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

Answer 3

I think that internal selection may help, for example:

 CREATE View AttendeeTableView AS SELECT (LastName || " " || FirstName) as AttendeeName, ( select CompanyName FROM 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,FirstName ORDER BY LastName, FirstName;