Getting a distinct comma-delimited string in T-SQL

Question

I'm stumped. Using SQL Server 2019.

SELECT * FROM (VALUES 
  (1, 'Alice', 'Alice'),
  (2, 'Robert', 'Robert'),
  (3, 'Robert', 'Bob'),
  (4, 'Richard', 'Rich'),
  (5, 'Richard', 'Rick'),
  (6, 'Richard', 'Dick')
) tmp (ID, RealName, NickName)

Given this I'd like to produce a table which shows each user and their possible nicknames. I'd like to the result to be:

Name	AKA
Alice	NULL
Robert	Bob
Richard	Rich, Rick, Dick

There are other similar questions on SO that I looked into but don't quite solve the problem of omitting the Name from the AKA column. TIA

SQL Distinct comma delimited list

Produce DISTINCT values in STRING_AGG

David Browne - Microsoft · Accepted Answer · 2021-04-02T20:44:02.097

Like this:

with q as
(
  SELECT * FROM (VALUES 
  (1, 'Alice', 'Alice'),
  (2, 'Robert', 'Robert'),
  (3, 'Robert', 'Bob'),
  (4, 'Richard', 'Rich'),
  (5, 'Richard', 'Rick'),
  (6, 'Richard', 'Dick')
  ) tmp (ID, RealName, NickName)
)
select RealName, 
       string_agg(case when RealName <> NickName then NickName else null end, ', ') AKA
from q
group by RealName

outputs

RealName AKA
-------- ---------------------
Alice    NULL
Richard  Rick, Rich, Dick
Robert   Bob

Getting a distinct comma-delimited string in T-SQL

1 Answers1