if OBJECT_ID('t_test','U') is not null drop table t_test
go
create table t_test
(
    ID int,
    sRemark varchar(2000)
)
go
insert into t_test(ID,sRemark)
select 1,'YI1203270018,YI1203290005,YI1203290006,YI1203290006,YC1203270049,' union all
select 2,'YI1111030008,YC1111030013,YC1111030012,YC1111210212,YC1111210212,' union all
select 3,'YI1112270006,YC1112270043,YI1201050013,YC1201050029,YI1201110025,' union all
select 4,'YC1202170017,YI1202230013,YC1202230302,YC1202280100,YC1202280107,' union all
select 5,'YI1111030001,YC1111030001,YC1111030003,YC1111030003,YI1111230006,' 
go



with cte as
(
    --一行转多行
    select 
        ID,
        convert(varchar(200),left(sRemark,CHARINDEX(',',sRemark)-1)) as col2,
        convert(varchar(200),RIGHT(sRemark,LEN(sRemark)-CHARINDEX(',',sRemark))) as temp
    from t_test
    union all
    select 
        ID,
        convert(varchar(200),left(temp,CHARINDEX(',',temp)-1)) as col2,
        convert(varchar(200),RIGHT(temp,LEN(temp)-CHARINDEX(',',temp))) as temp
    from cte where len(temp)>4
),
cte2 as
(
    --查询出出现重复的数据ID
    select 
        ID,
        col2
    from cte a
    group by a.ID,a.col2 
    having COUNT(col2)>1
)
    --关联查询
select 
    b.* 
from cte2 a
left join t_test b  on a.ID=b.ID
order by b.ID
--ID  sRemark
--1    YI1203270018,YI1203290005,YI1203290006,YI1203290006,YC1203270049,
--2    YI1111030008,YC1111030013,YC1111030012,YC1111210212,YC1111210212,
--5    YI1111030001,YC1111030001,YC1111030003,YC1111030003,YI1111230006,