CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2019
Dave B
Dave B

Reputation: 81

Getting a distinct comma-delimited string in T-SQL

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

Upvotes: 0

Views: 746

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89121

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

Upvotes: 5

Related Questions