sql - How to do multiple aggregated columns in a select statement -

To stop instant-ripplers, I tell that I am not using MySQL so I use it Can not GROUP_CONCAT, and I have already seen it already.

Example
(This is not the data with which I am working, but it accurately describes my case)
The individual table, With ID, name column
Person_Countries PersonID, Table Visited with Country Columns (1.m. Linking Table)
Person_StatesVisited Table, with PersonID, State Columns (1.minking table)

Obstacles
I can not modify the DB. I can not modify the code as much as possible. So, therefore, it should be done in SQL, not later run the query code.

desired result
Set a result with the column

Person.ID,
person .name,
[List of related person's list Country],
[List of related people: Prop. Protected.],

This can also be filtered by a normal search term that applies to all columns.

My attempt

Now, my SQL-Foo nowadays is not fast, but what I'm doing is based on examples of what I do I am here.

1) I have tried to use a scalar sub-query:

SELECT P.ID, P.Name,
(CV. Select ','
from the CV current PID on Person_Countries = CV.PixerID.
for the XML path ('')) given as a country,
(SELECT SV.State + ','
FROM Person_StatesSavigatSVOnP.IDs = SVPCairAid
XML Path ('for') Visited as States

By Person P

'%' + @SearchTerm + '%'
like WHERE P.Name or country '%' + @SearchTerm + '%'
preferred Like '%' + @ SearchTerm + '%'

which gives me the desired data, but only when I remove the WHERE clause it works with the WHERE section Does not because SQL Server 2005 does not like it to be that I am referring to the column created with a scalar subkey in the WHERE section, it is giving me an "invalid column name" countrywide "error" (and then states Invited column).

2) I have tried to apply cross Yes:

selection PIID, p. Name, Descriptive, Politician

Join PERSON
INNER Person_Countries PID on CV = CV. PersonID
Cross Application (
Selection CV. Person + ','
from person P2 join INNER Person_Countries P2.ID = CV on CV. PersonID
WHERE P.ID = P2.ID
For XML Path ('')
) PRE_TRIMMED (Countrywide)

Join INNER at PIID = SV at Person_StatesV.VV. PersonID
Apply Cross (
Join Person P2 INNER from SELECT SV.State + ','
, Person_StatesVVIT on P2.ID = SV.PersonID XML path to SV ('')
) PRE_TRIMMED (state permissions)

'%' + @ SearchTerm + '%'
like WHERE P.Name or country '%' + @SearchTerm + '%'
or '%' + @SearchTerm + '%'

like states but it does not work because you have many pre-transmissions Can not call.

Anyone have suggestions for me?

You need to nick your sub-names:

  With the form of X (SELECT P.ID, P.Name, (Select the CV. Country + ',' From 'on the Person_Countries CV current PID = XML path for CVPCING (' ')) Countries SELECT * FROM WHERE NAME '%' + @ (SELECT * FROM WHERE PERSON'S ASSOCIATED TO PERSON'S 'SVPCINGPMG PATH' ('')), on the Persistentials VV from Select SVST + ',' FM. SearchTerm + '%' '%' + @SearchTerm + '%' searched or '%' + @SearchTerm +% 'like states like' '%' ' 

You do something like this in another example You can. Actually in the second example, just something new by the name of another subkey, as PRE_TRIMMED2 .