CODEWITHSUNDEEP
sql-server
iSf5903
iSf5903

Reputation: 1

How does declaring and using a variable in TSQL select statement give a different result

I want to get a list of comma separated customer names. The below code only returns the last name

DECLARE @vfirstname NVARCHAR(500), @vdelimiter NVARCHAR(1)
SET @vdelimiter=','

SELECT @vfirstname = FirstName + @vdelimiter
FROM dbo.Customer c

SET @vfirstname = LEFT(@vfirstname, LEN( @vfirstname ) - 1);    
PRINT @vfirstname

I was able to fix it introducing the below statements (with help from google):

 SET @vfirstname ='' 
 SELECT @vfirstname = @vfirstname + FirstName + @vdelimiter
 FROM dbo.Customer c

But I am unable to understand the need to do this , how does the select statement really work in this scenario?

Upvotes: 0

Views: 46

Answers (1)

Brad
Brad

Reputation: 3591

This will do what you need without the substring function, the Select ',' is your delimiter

The stuff function removes the first comma/delimiter from the list for you using the XML path

-- use stuff instead of substringb
Select STUFF((
    SELECT ',' + FirstName
    FROM dbo.Customer
    FOR XML PATH('')
    ), 1, 1,'') 
FROM dbo.Customer
-- if you want a space after comma, need to update the STUFF to be 1, 2

Upvotes: 2

Related Questions