Looking for query run against table to output number of users and number of semicolon-separated items per user

Question

I have a table that looks something like the following:

|Id|Name                  |FormIds                                           |
|--|----------------------|--------------------------------------------------|
|1 |john.doe@blah.co      |32132;32323;232323;424323;2323;23232;2323         |
|2 |jane.doe@whatever.co  |32323;11123;11321                                 |
|3 |joe.blow@yeet.co      |22324                                             |

I'm looking for a query that will give me the number of distinct FormId's per user and last row showing total users count and total formids: Example output:

Names                 |TotalFormIdsPerUser                                      
----------------------|--------------------------
john.doe@blah.co      |7         
jane.doe@whatever.co  |3 
joe.blow@yeet.co      |1
----------------------|--------------------------
3                     |11

Thank you in advance!!

Answer 1

You can use string_split (For splitting the FormIds) and a CTE

Then you use group by to find the sum and sum total.

with _list as (
        select FormIds,Name,s.value value_
        from Testf a
        cross apply(select * from string_split(a.FormIds,';'))s
)
select Name as Names, c as TotalFormIdsPerUser                                      
from (
select  a.Name,count(a.value_) as c,1 as Type
from  _list a
group by a.Name

union all
select  
         cast( count(distinct a.Name) as varchar(100)) as name
        ,count(a.value_) as c
        ,2 as Type
from  _list a
)s
order by Type, c desc

Demo