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.

*"This was one of my interview questions on MS SQL."* With the greatest respect, it's not us being interviewed. If this is an interview question, show us what you've tried, and explain why it didn't work. — Thom A, Feb 26 '20 at 09:08
[How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/2029983) — Thom A, Feb 26 '20 at 09:09
There is no need for a recursive query. This can be done with a simple `group by` and string aggregation (`string_agg()`) — , Feb 26 '20 at 09:13

score 0 · Accepted Answer · answered Feb 26 '20 at 09:19

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

score 0 · Answer 2 · edited Feb 26 '20 at 11:21

declare @temp table(Name varchar(100))
insert into @temp 
values
('Ahar'), ('Aris'),('Suchi'),('Doll'),('Dipti')

select distinct substring(Name,1,1) FirstLetter,
  STUFF((SELECT distinct ', ' + t1.name
         from @temp t1
         where substring(t.Name,1,1) = substring(t1.Name,1,1)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') Name
from @temp t

This would lead to desired result.

score 0 · Answer 3 · answered Feb 26 '20 at 12:49

The answer to the question is:

select left(name, 1) as chr, count(*) as cnt,
       string_agg(name, ', ') within group (order by name) as names
from t
group by left(name, 1);

No recursive subqueries or temporary tables are needed -- in an interview setting, I would just assume that the person does not really understand SQL.

In older versions of SQL Server, you need to use XML or some other method to bring the strings together. That said, I would expect someone in an interview to know string_agg().

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

3 Answers3