将事务记录分组到一个单元格中

Question

我有两个表(公司和联系人)。我需要创建一个所有公司的列表(每个公司ID 1行)，每个公司ID的所有联系人列在一个单元格中，公司的每个不同联系人用换行符分隔。

我最好希望这是在访问中完成，因为表是从访问be数据库，但如果它更容易在MSQL中产生，那么我也可以这样做。

Answer 1

如果这是一个单独的专栏，那么有一篇你可能会感兴趣的文章

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Answer 2

对于SQL 2005+会这样做吗?

;with data as
(
SELECT  'Company1' AS company, 'Contact1' AS contact UNION ALL
SELECT  'Company1' AS company, 'Contact2' AS contact UNION ALL
SELECT  'Company1' AS company, 'Contact3' AS contact UNION ALL
SELECT  'Company1' AS company, 'Contact5' AS contact UNION ALL
SELECT  'Company2' AS company, 'Contact1' AS contact UNION ALL
SELECT  'Company3' AS company, 'Contact1' AS contact
)
SELECT
company
,STUFF((
SELECT ',' + contact
FROM data d2
WHERE d2.company = d1.company
FOR XML PATH('')), 1, 1, '') as contacts
FROM data d1
GROUP BY company