By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data
(
STUDENT_ID [nvarchar](50) NOT NULL,
SUBJECT_PASSING_FLG [nvarchar](50) NOT NULL,
testnumber int NOT NULL,
);
insert into data values
('123', 'Y,N,Y,N,N,Y', 1),
('123', 'Y,N,Y,N,Y,Y', 2),
('123', 'N,N,N,Y,N,N', 3),
('456', 'Y,Y,Y,Y,Y,Y', 2),
('789', 'Y,N,Y,N,Y,N', 1),
('789', 'N,Y,N,Y,N,Y', 3)
6 rows affected
select
STUDENT_ID,
STRING_AGG(case when total>0 then 'Y' else 'N' end, ',') WITHIN group ( order by num asc) as SUBJECT_PASSING_FLG
from (
select STUDENT_ID, num, sum(case value when 'Y' then 1 else 0 end) as total
from (
select
STUDENT_ID,
value,
(ROW_NUMBER() OVER(PARTITION by STUDENT_ID order by STUDENT_ID asc)-1)%6+1 as num
from data
cross APPLY STRING_SPLIT(SUBJECT_PASSING_FLG, ',')
) as q
group by STUDENT_ID, num
) as p
group by STUDENT_ID
| STUDENT_ID | SUBJECT_PASSING_FLG |
|---|---|
| 123 | Y,N,Y,Y,Y,Y |
| 456 | Y,Y,Y,Y,Y,Y |
| 789 | Y,Y,Y,Y,Y,Y |