0

all. I've just discovered the stuff function in SQL Server, and I'm having difficulty using it the correct way. As you can see, it just lists all the teams out for each row. I've created a sample set of data to better understand my bigger set of data I'm working with.

DECLARE @SportsSurvey table(surveyid int , persID int, city varchar(20),favteam varchar(20))                                                   

INSERT @SportsSurvey (surveyid, persID, city, favteam) values                                                

('1234','1','Atlanta','Hawks')                                                   

,('2345','3','Dallas', 'Cowboys')                                                

,('3456','4','NYC', 'Nets')                                               

,('4567','1','Atlanta', 'Falcons')                                               

,('5678','5','LA', 'Lakers')                                                     

,('6789','7','LA', 'Dodgers')

,('7890','6','Chicago', 'Bears')

,('1234','1','Atlanta', 'Braves')

,('7890','6','Chicago','Cubs')

,('7890','6','Chicago','Bulls')





select

   distinct  surveyid, persid, city,

    stuff((

        select (', ' + s.favteam)

        from @SportsSurvey s

        where s.favteam = favteam

              --group by surveyid

        order by s.favteam

        for xml path('')

                     ),1,1,'') as teams

from @SportsSurvey



/***********************************************/



/***********************************************/



DECLARE @SportsSurvey2 table(surveyid int , persID int, city varchar(20),favteam varchar(100))                                                 

INSERT @SportsSurvey2 (surveyid, persID, city, favteam) values      





('1234','1','Atlanta','Braves, Hawks')

,('4567','1','Atlanta','Falcons')

,('2345','3','Dallas','Cowboys')

,('3456','4','NYC','Nets')

,('5678','5','LA','Lakers')

,('6789','7','LA','Dodgers')

,('7890','6','Chicago','Bears, Bulls, Cubs')







select *

from @SportsSurvey2

I've also provided the output I'd like to see. Can someone steer me in the right direction? Using SQL Server Management Studio version 15.0

Thank you!

Outputs

Thom A
  • 88,727
  • 11
  • 45
  • 75
Cameron
  • 7
  • 6
  • 3
    The version of Management Studio isn't relevant. The version of SQL Server more so, as `STRING_AGG` is available in SQL Server 2017. What does `SELECT @@VERSION` give you? – Jeroen Mostert Nov 20 '19 at 15:10
  • 2
    This isn't anything about `STUFF` and is about `FOR XML PATH`. All `STUFF` does here is removes the leading `,` character for a string that looks like `',Value1,Value2,Value3'` – Thom A Nov 20 '19 at 15:12
  • As for the problem, your subquery is lacking a `WHERE`; therefore you're creating a delimited list of **every** value in the table. You need to use a correlated subquery, like shown in the duplicate (which links to multiple other duplicates). – Thom A Nov 20 '19 at 15:13
  • The stuff portion is really used to remove the leading ', ' which in your case is 2 characters. So to remove both you should have ----> ),1,2,'') as teams – KeithL Nov 20 '19 at 15:36
  • select surveyid, persid, city, stuff(( select (', ' + s2.favteam) from SportsSurvey s2 where s1.persID=s2.persID --group by surveyid order by s2.favteam for xml path('') ),1,2,'') as teams from SportsSurvey s1 group by surveyid, persid, city – KeithL Nov 20 '19 at 15:46
  • Your biggest flaw was not associating the correlated subquery with the outside query on personID – KeithL Nov 20 '19 at 15:51
  • Thank you all for the feedback. I have SQL Server 2012. I'm a rookie/young professional so I appreciate the feedback. This is my first post. – Cameron Nov 20 '19 at 16:24

0 Answers0