需求
将老师一周中同一大节的课表信息整理到同一条记录里,不同星期对应不同的列。(涉及到字符串拼接、行转列)
常见的行转列函数有vm_concat、listagg、xmlagg等,但是oracle12g不支持vm_concat,而且前两个函数在拼接字符串的时候容易出现拼接结果过长、字符串缓冲区过小等问题,难搞。xmlagg就可以完美解决这个问题,拼接字段中出现null也不影响,唯一的不足就是多个null拼接时,xmlagg并不能自动省去,最后需要处理下多余的拼接符号,不然难看。
xmlagg函数用法
xmlagg函数需要将输入的值转换为xml,处理返回结果也是xml,最后再用getclobval()获取colb类型的结果。
xmlagg(xmlelement(表名, 输入值1,输入值2) order by 参数).extract('//text()').getclobval()
输入值就是要拼接的字段和连接符,顺序随意,xmlagg就是把两个输入值拼在一起,连接符在前在后都一样,最后都是要处理下多余的字符的
listagg函数用法
listagg(参数, '分隔符') within group(order by 参数)
vm_concat函数用法
vm_concat(参数)
CREATE OR REPLACE VIEW LY_VIEW_MHZS_JSKCB11 AS
SELECT
XN,XQ,JC,GH,DSZ,
--将同一节次的课程信息进行列转行并去除多余的连接符
TRIM(',' FROM XMLAGG(XMLELEMENT(T, ',', T.AKC) ORDER BY XN,XQ,JC,GH,DSZ).EXTRACT('//text()').GETCLOBVAL()) AS AKC,
TRIM(',' FROM XMLAGG(XMLELEMENT(T, ',', T.BKC) ORDER BY XN,XQ,JC,GH,DSZ).EXTRACT('//text()').GETCLOBVAL()) AS BKC,
TRIM(',' FROM XMLAGG(XMLELEMENT(T, ',', T.CKC) ORDER BY XN,XQ,JC,GH,DSZ).EXTRACT('//text()').GETCLOBVAL()) AS CKC,
TRIM(',' FROM XMLAGG(XMLELEMENT(T, ',', T.DKC) ORDER BY XN,XQ,JC,GH,DSZ).EXTRACT('//text()').GETCLOBVAL()) AS DKC,
TRIM(',' FROM XMLAGG(XMLELEMENT(T, ',', T.EKC) ORDER BY XN,XQ,JC,GH,DSZ).EXTRACT('//text()').GETCLOBVAL()) AS EKC,
TRIM(',' FROM XMLAGG(XMLELEMENT(T, ',', T.FKC) ORDER BY XN,XQ,JC,GH,DSZ).EXTRACT('//text()').GETCLOBVAL()) AS FKC,
TRIM(',' FROM XMLAGG(XMLELEMENT(T, ',', T.GKC) ORDER BY XN,XQ,JC,GH,DSZ).EXTRACT('//text()').GETCLOBVAL()) AS GKC
FROM (
SELECT
GH, --工号
A.XN||'-'||TO_CHAR(A.XN+1) AS XN, --学年
CASE JC IS NOT NULL THEN '第'||JC||'节' ELSE '【-】' END AS JC, --节次
XQ , --学期
DSZ, --单双周
--拼接课程信息
CASE WHEN XQJ='1' THEN '第'||ZC||'周【'||KCMC||'】'||SKDD||B.BJMC ELSE NULL END AS AKC,--星期一课程
CASE WHEN XQJ='2' THEN '第'||ZC||'周【'||KCMC||'】'||SKDD||B.BJMC ELSE NULL END AS BKC,--星期二课程
CASE WHEN XQJ='3' THEN '第'||ZC||'周【'||KCMC||'】'||SKDD||B.BJMC ELSE NULL END AS CKC,--星期三课程
CASE WHEN XQJ='4' THEN '第'||ZC||'周【'||KCMC||'】'||SKDD||B.BJMC ELSE NULL END AS DKC,--星期四课程
CASE WHEN XQJ='5' THEN '第'||ZC||'周【'||KCMC||'】'||SKDD||B.BJMC ELSE NULL END AS EKC,--星期五课程
CASE WHEN XQJ='6' THEN '第'||ZC||'周【'||KCMC||'】'||SKDD||B.BJMC ELSE NULL END AS FKC,--星期六课程
CASE WHEN XQJ='7' THEN '第'||ZC||'周【'||KCMC||'】'||SKDD||B.BJMC ELSE NULL END AS GKC--星期日课程
FROM JSKB A
LEFT JOIN
(
--拼接班级信息
SELECT A.XN,A.XQ,A.SKBJ,
SUBSTR(XMLAGG(XMLELEMENT(A, ',', A.BJMC) ORDER BY A.XN,A.XQ,A.SKBJ).EXTRACT('//text()').GETCLOBVAL(),2) AS BJMC
FROM BJXX A
GROUP BY A.XN,A.XQ,A.SKBJ
) B ON A.SKBJ=B.SKBJ AND A.XN=B.XN AND A.XQ=B.XQ
ORDER BY A.XN,A.XQ,A.ZJJS_GH,A.JCINFO
) T
GROUP BY XN,XQ,JC,GH,DSZ;