将一个字段”,“分割的数据进行多列展示;
SELECT
REGEXP_SUBSTR (T.TEXTBOOK_IDS,'[^,]+',1,LEVEL) TEXTBOOK_ID
FROM
(SELECT
COURSE_TEXTBOOKS.TEXTBOOK_IDS
FROM
COURSE_TEXTBOOKS COURSE_TEXTBOOKS
) T
CONNECT BY LEVEL <= LENGTH (T .TEXTBOOK_IDS) - LENGTH (REPLACE (T .TEXTBOOK_IDS, ',', '')) + 1 ;
将多行数据合并成一个字段展示
listagg(TEXTBOOK.NAME_ZH, ',') within group(order by TEXTBOOK.NAME_ZH)
多行转多列
SELECT * FROM (
SELECT grade_id, sub_grade_type_id, score
FROM sub_grade sub
) PIVOT (
MAX(score) FOR sub_grade_type_id IN (1 AS a, 2 AS b, 3 AS c, 5 AS d) -- 指定要转换成列名的值
) ORDER BY grade_id;