Display Multiple Rows As One

Inigo Montoya 586 Reputation points
2021-04-22T18:12:21.473+00:00

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
    2021-04-22T18:24:25.223+00:00
    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

    No comments

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee
    2021-04-23T01:46:14.3+00:00

    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.

    No comments