How to find the Count of each value in a column in sql

create table alphabet
(testword varchar(10)
)

insert into alphabet values('AS,BS')
insert into alphabet values('AS,CS')
insert into alphabet values('CS,ES')
insert into alphabet values('DE,AS')


alter Procedure [dbo].[asp_CommaSeparatedStringToTable]
AS
BEGIN
  declare @rownum as int
  DECLARE @String VARCHAR(10)
  DECLARE @StringInput VARCHAR(100)
  set @rownum =1
CREATE TABLE #temp1
(
   COL1 varchar(50),  
   rownum int, 
)
CREATE TABLE #temp2
(
   COL1 varchar(50),   
)
Insert into #temp1
  select testword,ROW_NUMBER() over (order by testword) as rowno from alphabet
  
  while (@rownum <=(select MAX(rownum) from #temp1))
  BEGIN
 
select @StringInput=COL1 from #temp1 where rownum=@rownum

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO  #temp2
        VALUES ( @String )
    END
    set @rownum=@rownum+1
    END
    
    select COUNT(1),col1 from #temp2 group by col1
     drop table #temp2
     drop table #temp1
END
GO

exec [asp_CommaSeparatedStringToTable]