How to do multiple aggregated columns in a select statement

Just to head off the quick-repliers, I'll mention that I'm not using MySQL so I can't use GROUP_CONCAT, and that I've also looked at this question already.

The example

(this is not the data i'm working with but it accurately describes my case)

Person table, with ID, Name columns

Person_CountriesVisited table, with PersonID, Country columns (1..M linking table)

Person_StatesVisited table, with PersonID, State columns (1..M linking table)

The constraints

I can't modify the DB. I can't modify much of the code, either, so this has to be done in SQL, not in code after running the query.

The desired result

A result set with the columns

Person.ID,

Person.Name,

[list of related Person_CountriesVisited.Country],

[list of related Person_StatesVisited.State],

which can also be filtered by a generic search term which applies across all columns.

My attempts

Now, my SQL-fu isn't all that sharp nowadays, but I'm doing what I can based on examples I've found.

1) I've tried using a scalar sub-query:

SELECT P.ID, P.Name,

( select CV.Country + ', '

FROM Person_CountriesVisited CV ON P.ID = CV.PersonId

FOR XML PATH ('')) AS CountriesVisited,

( SELECT SV.State + ', '

FROM Person_StatesVisited SV ON P.ID = SV.PersonId

FOR XML PATH ('')) AS StatesVisited

FROM Person P

WHERE P.Name LIKE '%' + @SearchTerm + '%'

OR CountriesVisited LIKE '%' + @SearchTerm + '%'

OR StatesVisted LIKE '%' + @SearchTerm + '%'

Which returns the data I want, but only when I remove the WHERE clause. It doesn't work with the WHERE clause because SQL Server 2005 doesn't seem to like that i'm referencing a column created from a scalar subquery in the WHERE clause. It's giving me an "Invalid column name 'CountriesVisit开发者_JAVA百科ed'" error (and again for the StatesVisited column).

2) I've tried the cross apply trick:

SELECT P.ID, P.Name, CountriesVisited, StatesVisited

FROM Person P

INNER JOIN Person_CountriesVisited CV ON P.ID = CV.PersonID

CROSS APPLY (

SELECT CV.Country + ', '

FROM Person P2 INNER JOIN Person_CountriesVisited CV ON P2.ID = CV.PersonID

WHERE P.ID = P2.ID

FOR XML PATH ('')

) PRE_TRIMMED (CountriesVisited)

INNER JOIN Person_StatesVisited SV ON P.ID = SV.PersonID

CROSS APPLY (

SELECT SV.State + ', '

FROM Person P2 INNER JOIN Person_StatesVisited SV ON P2.ID = SV.PersonID FOR XML PATH ('')

) PRE_TRIMMED (StatesVisited)

WHERE P.Name LIKE '%' + @SearchTerm + '%'

OR CountriesVisited LIKE '%' + @SearchTerm + '%'

OR StatesVisted LIKE '%' + @SearchTerm + '%'

But that doesn't work either since you apparently can't have multiple pre_trimmed calls.

Anybody have suggestions for me?

You just need to alias your subqueries:

WITH x AS (
  SELECT P.ID
        ,P.Name
        ,( select CV.Country + ', '
         FROM Person_CountriesVisited CV ON P.ID = CV.PersonId 
         FOR XML PATH ('')
        ) AS CountriesVisited
        ,( SELECT SV.State + ', '
         FROM Person_StatesVisited SV ON P.ID = SV.PersonId 
         FOR XML PATH ('')
        ) AS StatesVisited
  FROM Person P
)
SELECT *
FROM X    
WHERE Name LIKE '%' + @SearchTerm + '%'
  OR CountriesVisited LIKE '%' + @SearchTerm + '%'
  OR StatesVisted LIKE '%' + @SearchTerm + '%'

You can do something similar in the second example. Actually in the second example, just alias the second subquery as something new, like PRE_TRIMMED2.