Display Multiple Rows As One

Question

I have the below query, how can I have it show each name on one row with a ' - ' between them? Meaning in this sample data I'd expect to have Jason - Mark displayed. Order doesn't matter, just as long as they are both displayed with the " - " between them

DRop Table #Test
  Create Table #Test
  (
     name varchar(100)
     ,manager varchar(100)
  )

  Insert Into #Test Values ('Jason', 'Joe'), ('Mark', 'Joe')

  Select name from #Test where manager = 'Joe'

Answer 1

Select Distinct
  (Select Stuff(
    (Select ' - ' + name From #Test t1 Where t.manager = t1.manager Order By t1.name 
    For XML Path(''),Type)
    .value('text()[1]','nvarchar(max)'),1,2,N'')) As Names
From #Test t
Where t.manager = 'Joe';

Tom

Answer 2

Hi InigoMontoya-1790,

Glad that you already received your answer!

If your version is SQL Server 2017 and later, you could also consider to use STRING_AGG as below:

Select string_agg(name,' - ') Names from #Test where manager = 'Joe'  

Output:

Names  
Jason - Mark  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.