Reputation: 1673
I'm mainly a presentation/logic tier developer and don't mess around with SQL all that much but I have a problem and am wondering if it's impossible within SQL as it's not a full programming language.
E.G
ContactID - CompanyID - CompanyName
***********************************
1 001 Lol
1 002 Haha
1 003 Funny
2 002 Haha
2 004 Lmao
I want to return
ContactID - Companies
*********************
1 Lol, Haha, Funny
2 Haha, Lmao
I have found the logic to do so with ONE ContactID at a time:
SELECT x.ContactID, substring(
(
SELECT ', '+y.CompanyName AS [text()]
FROM TblContactCompany x INNER JOIN TblCompany y ON x.CompanyID = y.CompanyID WHERE x.ContactID = 13963
For XML PATH (''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 3, 1000)
[OrgNames] from TblContact x WHERE x.ContactID = 13963
As you can see here, I am hardcoding in the ContactID 13963, which is neccessary to only return the companies this individual is linked to.
The issue is when I want to return this aggregate information PER ROW on a much bigger scale SELECT (on a whole table full of ContactID's).
I want to have x.ContactID = (this.ContactID)
but I can't figure out how!
Failing this, could I run one statement to return a list of ContactID's, then in the same StoredProc run another statement that LOOPS through this list of ContactID's (essentially performing the second statement x times where x = no. of ContactID's)?
Any help greatly appreciated.
Upvotes: 2
Views: 348
Reputation: 3197
I guess all you need to do is to use unique table identifiers in your subquery and join the table in subquery with outer table x:
SELECT x.ContactID, substring(
(
SELECT ', '+z.CompanyName AS [text()]
FROM TblContactCompany y, TblCompany z WHERE y.CompanyID = z.CompanyID AND y.ContactId = x.ContactId
For XML PATH (''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 3, 1000)
[OrgNames] from TblContact x
Upvotes: 1
Reputation: 6171
Don't loop or you will get performance problems (row by agonising row RBAR). Instead do set based queries.
This is untested but should give you an idea of how it may work:
SELECT
x.ContactID,
substring(
(SELECT ', '+y.CompanyName AS [text()]
FROM TblContactCompany y
WHERE x.CompanyID = y.CompanyID
For XML PATH (''), root('MyString'), type).value('/MyString[1]','varchar(max)')
, 3, 1000)
[OrgNames]
FROM TblContact x
And I have a feeling you can use CONCAT instead of substring
Upvotes: 0
Reputation: 1271241
You want a correlated subquery:
SELECT ct.ContactID,
stuff((SELECT ', ' + co.CompanyName AS [text()]
FROM TblContactCompany cc INNER JOIN
TblCompany co
ON cc.CompanyID = co.CompanyID
WHERE cc.ContactID = ct.ContactId
For XML PATH (''), root('MyString'), type
).value('/MyString[1]', 'varchar(max)'),
1, 2, '')
[OrgNames]
from TblContact ct;
Note the where
clause on the inner subquery.
I also made two other changes:
x
in the outer query and the inner query.)substring()
with stuff()
, which does exactly what you want.Upvotes: 4
Reputation: 2328
You could use a table variable to store the required x.ContactID
and in your main query in the WHERE
clause use IN
clause like below
WHERE
...
x.ContactID IN (SELECT ContactID FROM @YourTableVariable)
Upvotes: 1