Display Multiple Rows As One

Inigo Montoya 586 Reputation points

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'
No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,026 Reputation points
    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';


    No comments

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee

    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'  


    Jason - Mark  

    Best regards

    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.

    No comments