0

I've seen several questions about how to pull together multiple rows into a single comma-separated column with t-sql. I'm trying to map those examples to my own case in which I also need to bring columns from two tables together referencing a junction table. I can't make it work. Here's the query I have:

WITH usersCSV (userEmails, siteID) 
AS (SELECT usersSites.siteID, STUFF(
        (SELECT ', ' + users.email 
        FROM users 
        WHERE usersSites.userID = users.id
        FOR XML PATH ('')
        GROUP BY usersSites.userID
    ), 1, 2, '')
    FROM usersSites
    GROUP BY usersSites.siteID
)
SELECT * FROM usersCSV

The hard stuff here is based on this answer. I've added the WITH which, as I understand it, creates a sort of temporary table (I'm sure it's actually more complicated than that, but humor me.) to hold the values. Obviously, I don't need this just to select the values, but I'm going to be joining this with another table later. (The whole of what I need to do is still more complicated than what I'm trying to do here.)

So, I'm creating a temporary table named usersCSV with two columns which I'm filling by selecting the siteID column from my usersSites table (which is the junction table between users and sites) and selecting ', ' + users.email from my users table which should give me the email address preceded by a comma and space. Then, I chop the first two characters off that using STUFF and group the whole thing by usersSites.siteID.

This query gives me an error identifying line 5 as the problem area:

Column 'usersSites.userID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why should this matter since the column in question is actually in the WHERE rather than the SELECT as is stated in the error? How can I fix it? I need only the users with an ID that matches an ID in the junction table. I've got tons of users that aren't mapped in that table and have no need to select them.

tl;dr- I need a temp table with distinct sites in one column and a comma-separated list of the email addresses of related users in the other. These two pieces of data come from other tables and will be put together using a junction table on the primary keys of those two tables. Hope that makes sense.

Community
  • 1
  • 1
raddevon
  • 3,290
  • 4
  • 39
  • 49
  • 1
    Three options I wonder if you have considered: 1) doesn't SQL server have the "list" aggregate function? 2) hierarchical query using recursive 'common table expressions'; 3) aggregating T-SQL function? – Andrew Wolfe May 30 '14 at 00:43

1 Answers1

2
select distinct us.siteId,
STUFF((select ', ' + u.email
        from users u 
        join usersSites us2 on us2.userId = u.userId
        where us2.siteId = us.siteId    
        for xml path('')), 1, 2, '')
from usersSites us

SQL Fiddle

Jerrad
  • 5,240
  • 1
  • 18
  • 23