1

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.

Cliff
  • 697
  • 8
  • 19

1 Answers1

3

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

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

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:

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:

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

Community
  • 1
  • 1
GarethD
  • 68,045
  • 10
  • 83
  • 123
  • 1
    These GROUP_CONCAT queries are always a race. :-) +1 and also +1 for your explanation in the other answer. – Aaron Bertrand Jan 24 '14 at 20:33
  • You are the wind beneath my wings. – Cliff Jan 24 '14 at 21:22
  • @AaronBertrand Ha, a race I am not overly proud of winning given I answered at 8.30pm on a Friday night. Perhaps I need to get out more. Thanks though. – GarethD Jan 24 '14 at 23:09
  • It is the STUFF function, and removing the first character was by design. The first character is `;` which I intended to be cut off, so you end up with a list like "ID1: Name1;ID2: Name2;ID3: Name3", not ";ID1: Name1; ID2:...". If you have changed what is inside the subquery then yes it may well be removing the first character. – GarethD Jan 27 '14 at 17:31