SQLITE：如果行共享一列，则将行合并为单行 [英] SQLITE: merging rows into single row if they share a column

问题描述

从上一篇文章，我在sqlite3中有以下视图：

From a 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 a many-to-many relation 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 I'd like to do is, in cases where there's more than one company (like above), create a query that outputs:

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

另一个输出：

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

所以我需要知道如何合并具有不同
值。

So I need to know essentially how to merge a specific column for rows that have different values in that table.

有任何想法吗？

c $ c>在第一个查询中显然是文本串联，也就是说，（row1.CompanyName |||| row2.CompanyName） p>


Just in case, company A company B in the first query is obviously text concatenation, That is, something along the lines of (row1.CompanyName || " " || row2.CompanyName)

推荐答案

现在我明白你的意思了。使用聚合函数 group_concat（X）。

更多在手册。

Now I see what you mean. Use the aggregate function group_concat(X) for that.
More 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 using table aliases to make it shorter and easier to read.

这篇关于SQLITE：如果行共享一列，则将行合并为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！