CODEWITHSUNDEEP
sqlsql-serverdynamic
aspirant_sensei
aspirant_sensei

Reputation: 1673

SQL Server dynamically change WHERE clause in a SELECT based on returned data

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

Answers (4)

Michał Turecki
Michał Turecki

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

Ruskin
Ruskin

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

Gordon Linoff
Gordon Linoff

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:

  1. I changed the table aliases to better represent the table names. This makes queries easier to understand. (Plus, the aliases had to be changed because you were using x in the outer query and the inner query.)
  2. I replaced the substring() with stuff(), which does exactly what you want.

Upvotes: 4

Dimt
Dimt

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

Related Questions