0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
iSf5903
  • 1
  • 4

1 Answers1

2

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
Brad
  • 3,454
  • 3
  • 27
  • 50
  • I would recommend this method instead as well. For further reading, try: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server – Atmira Jan 03 '20 at 19:57
  • Can also use [`STRING_AGG()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) if you have a new enough version. – BJones Jan 03 '20 at 21:38