5

I'm relatively new to T-SQL and looking through my predecessors scripts, I found a line I can't quite parse. It's a hard thing to look up on my own. Hoping for advice.

This line occurs inside a WHILE loop, but I don't think that matters. I believe functionally, that this is gathering the values from a column across a few rows that share an ID and concatenating them with commas into 1 field. But I don't know what this is called or how to look it up to use it myself.

What is this 'x' doing at the end? Can anyone point me to information on this?

(select @invtid = coalesce(@invtid + ',',' ')+rtrim(RoleID) 
FROM (
    Select distinct roleid 
    from UserAccounts 
    WHERE CustID = @custID
     )
 x)

Thanks.

  • 1
    This is not a safe approach to concatenating strings BTW. It can suddenly fail if the execution plan changes. – Martin Smith Aug 7 '17 at 19:28
8

The "X" is an alias for the following portion:

Select distinct roleid 
from UserAccounts 
WHERE CustID = @custID

So you'd be able to use it as:

SELECT x.roleid
FROM  
(    Select distinct roleid 
from UserAccounts 
WHERE CustID = @custID) x
where x.roleid >100

In the grand scheme of things, your query would look like:

 (select @invtid = coalesce(@invtid + ',',' ')+rtrim(x.RoleID) 
 FROM (
    Select distinct roleid 
    from UserAccounts 
    WHERE CustID = @custID
     )x
 )

technically you can use anything as your Alias. "X", usually a more descriptive alias works. Something like "roles"

  • 1
    Well, I feel like a dummy. Somehow the context of the script blinded me to the obvious SELECT [whatever] FROM [whatever] X <-- ALIAS. My predecessor used a pretty fixed set of aliases, so the vague 'x' threw me. Thanks for the help. – jbz Aug 7 '17 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.