在SQL Server 2008中连接不确定数量的字符串

I need to send out an email in bulk, with the email containing each course associated with that particular user's email. The following query will return a list of all courses and users associated with them.

我需要批量发送一封电子邮件,其中包含与该特定用户的电子邮件相关联的每个课程的电子邮件。以下查询将返回与其关联的所有课程和用户的列表。

select (cm.course_id + ': '+ cm.course_name) as course,
u.lastname, u.firstname, u.email from course_users cu
join users u on u.pk1 = cu.users_pk1
join course_main cm on cm.pk1 = cu.crsmain_pk1

The trouble is that for the Mail Merge to work properly, I need each user listed only once, and all associated courses in a single (delimited) cell for that row.

问题是,为了使邮件合并正常工作,我需要每个用户只列出一次,并且所有相关课程都在该行的单个(分隔)单元格中。

Any help is appreciated!

任何帮助表示赞赏!

EDIT: Removing text about the CONCAT() function because it's just distracting.

编辑:删除有关CONCAT()函数的文本,因为它只是分散注意力。

1 个解决方案

#1

You can use SQL Server's XML extensions to concatenate rows:

您可以使用SQL Server的XML扩展来连接行:

SELECT  Course = STUFF((SELECT  ';' + cm.course_id + ': '+ cm.course_name
                        FROM    course_users cu
                                INNER JOIN course_main cm
                                    ON cm.pk1 = cu.crsmain_pk1
                        WHERE   u.pk1 = cu.users_pk1
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        u.lastname, 
        u.firstname, 
        u.email
FROM    users u;

For a more detailed explanation of how this works see this answer.

有关其工作原理的更详细说明,请参阅此答案。

Example on SQL Fiddle

关于SQL小提琴的例子

EDIT

To limit this to only users on a course you can move the logic to within an APPLY, then check the result is not null:

要将此限制仅限于课程中的用户,您可以将逻辑移到APPLY中,然后检查结果是否为空:

SELECT  Course = STUFF(c.course.value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        u.lastname, 
        u.firstname, 
        u.email
FROM    users u
        CROSS APPLY
        (   SELECT  ';' + cm.course_id + ': '+ cm.course_name
            FROM    course_users cu
                    INNER JOIN course_main cm
                        ON cm.pk1 = cu.crsmain_pk1
            WHERE   u.pk1 = cu.users_pk1
            FOR XML PATH(''), TYPE
        ) c (course)
WHERE   c.course IS NOT NULL;

or add an exists clause:

或者添加一个exists子句:

SELECT  Course = STUFF((SELECT  ';' + cm.course_id + ': '+ cm.course_name
                        FROM    course_users cu
                                INNER JOIN course_main cm
                                    ON cm.pk1 = cu.crsmain_pk1
                        WHERE   u.pk1 = cu.users_pk1
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        u.lastname, 
        u.firstname, 
        u.email
FROM    users u
WHERE   EXISTS (SELECT 1 FROM course_users cu WHERE u.pk1 = cu.users_pk1);

Examples of both on SQL Fiddle

关于SQL Fiddle的例子

#1