表(TBStudent)结构及数据如下:
现在要将表变成如下格式:
当然也可以写函数来实现合并行或者列,请各位多多指教。
姓名 | 科目 | 成绩 |
张三 | 语文 | 90 |
张三 | 数学 | 80 |
张三 | 英语 | 100 |
姓名 | 各科成绩 |
张三 | 语文:90,数学:80,英语:100 |
select studentname,TRANSLATE(LTRIM (text, '/'), '*/', '*,')各科成绩 from
(SELECT ROW_NUMBER () OVER (PARTITION BY studentname ORDER BY studentname,lvl DESC)rn,studentname,text
FROM (SELECT studentname, LEVEL lvl,
SYS_CONNECT_BY_PATH (各科成绩,'/') text
FROM (SELECT studentname, (subject||':'||subjectscore) as 各科成绩,
ROW_NUMBER () OVER (PARTITION BY studentname ORDER BY studentname) x
FROM TBStudent
group by studentname,subject,subjectscore
ORDER BY studentname) a
CONNECT BY studentname= PRIOR studentname AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY studentname;
当然也可以写函数来实现合并行或者列,请各位多多指教。