考虑以下数据：
STUDENT_ID SUBJECT_PASSING_FLG testnumber
123 YNYNNY 1
123 YNYNYY 2
123 NNNYNN 3
456 YYYYYY 2
789 YNYNYN 1
789 NYNYNY 3
预期输出：
STUDENT_ID SUBJECT_PASSING_FLG
123 YNYYYY
456 YYYYYY
789 YYYYYY
SUBJECT_PASSING_FLG中的每个字符表示每个受试者的结果，测试编号表示尝试测试的序列号。
我们想找到最终结果，因为如果学生在科目中至少通过了一次考试，那么将标志设置为Y else N
因为您没有提到您使用的数据库。我在sql server 2019中尝试过。
只有当SUBJECT_PASSING_FLG字符串中有分隔符时，我才能找到解决方案。
模式
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)
sql
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
dbfiddle
insert into dataval values
(1,'YYNY',1),
(1,'YNNY',2),
(1,'NYNY',3),
(2,'YNNY',1),
(3,'YNNY',1),
(4,'YNNY',3),
(3,'YYNY',2),
(4,'YNYY',3);
WITH cte_seq AS (
SELECT 1 AS seq
UNION ALL
SELECT seq + 1 AS seq FROM cte_seq WHERE seq < 4
), cross_cte AS (
SELECT * FROM cte_seq CROSS apply dataval
), flag_wise_status AS (
SELECT student_id,
testnumber,
Substring(subject_passing_flg, seq, 1) AS subject_status,
seq AS subject_num
FROM cross_cte
), max_flag AS (
SELECT student_id,
subject_num,
Max(subject_status) AS subject_status
FROM flag_wise_status
GROUP BY student_id, subject_num
)
SELECT student_id,
String_agg(subject_status, '') AS result
FROM max_flag
GROUP BY student_id;
结果：
1 YYNY
2 YNNY
3 YYNY
4 YNYY
dbfiddle