Display all the values which start with first letter of each string separated by any delimiter

Question

I have input column name

Ahar
Aris
Suchi
Doll
Dipti

I want the output to be with two columns

FirstLetter TotalsWordsWithThatLetter

A Ahar, Aris
S Suchi
D Doll,Dipti

I know recursive CTE/stuff function will be of help. But unable to write complete code.

Answer 1

This is how you can do it.

Declare a temp table with char and name, then using substring function of sql put the first character and name in the table. From there you can group the results.

Here is the sample for you for mssql server

declare @tbl table(ch char(1), name nvarchar(50))

insert into @tbl
select ( SELECT SUBSTRING(e.address, 1, 1)),address from Emp2 e

select ch,name From @tbl
group by ch, name

select distinct t.[ch],
  STUFF((SELECT distinct ', ' + t1.name
         from @tbl t1
         where t.[ch] = t1.[ch]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from @tbl t