try this..
--My table
Create table empotest
(Employeeid int,
Level varchar(100))
insert into empotest values (101,'1,2,3')
insert into empotest values (102,'1,2,3')
insert into empotest values (103,'4,5,6')
drop table #temptest
DECLARE @Count INT
Declare @totalcount int
Declare @recordcount int
declare @string varchar(100)
declare @EmpId int
SET @Count = 0
set @recordcount=1
create table #temptest
(Empid int,
Level int)
select @totalcount=count(*) from empotest where Level is not null
select @totalcount
While @recordcount<=@totalcount
Begin
SET @Count = 0;
With CTE_test as
(select employeeid,level,Row_Number() over (order by EmployeeId asc) as rn from empotest)
select @String=level,@EmpId=employeeid from cte_test where rn=@recordcount
WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
INSERT into #temptest values (@EmpId,SUBSTRING(@String,@Count,1))
END
SET @Count = @Count + 1
END
SET @recordcount = @recordcount + 1
END
select Level,count(Empid) from #temptest group by level
drop table #temptest
Table:
101 1,2,3
102 2,3
105 4,5,6
Output
1 1
2 2
3 2
4 1
5 1
6 1