方案一:wm_concat函数
select username, id, wmsys.wm_concat(subject) as subject, wmsys.wm_concat(score) as score
from STUDENTSCORES
group by username, id
方案二:listagg函数
select username, id, LISTAGG(subject, ‘-‘) within group(order by subject) as subject, LISTAGG(score, ‘,‘) within group(order by score) as score
from STUDENTSCORES
group by username, id
方案三:常规sql
select username, id, translate(ltrim(subject, ‘/‘), ‘*/‘, ‘*,‘) as subject,translate(ltrim (score, ‘/‘), ‘*/‘, ‘*,‘) as score
from
(select row_number() over (partition by username, id order by username, id, lvl desc) as rn, username, id, subject, score
from
(select username, id, level lvl, sys_connect_by_path (subject, ‘/‘) as subject, sys_connect_by_path (score, ‘/‘) as score
from
(select username, id, subject, score, row_number() over (partition by username,id order by username, id) as num from STUDENTSCORES order by username, id)
connect by username = prior username and id = prior id and num - 1 = prior num))
where rn = 1;
注意:
方案一中默认分隔符为 ‘,’
方案二只适合11g之后的版本
相关文章
- 09-30Notepad++将多行数据合并成一行
- 09-30将txt多行文本合并成一行
- 09-30数据库多行转一行
- 09-30SqlServer中 Partition By 的使用( 对多行数据分组后排序取每个产品的第一行数据)
- 09-30Hive中将数据一行转多行、多行转一行、再转为以逗号分隔的string类型
- 09-30数据的行列变化:多行数据到一行数据中
- 09-30数据库一列多行转一行多列
- 09-30数据库中多行数据合并成一行
- 09-30查出了a表,然后对a表进行自查询,a表的别名t1,t2如同两张表,因为t1,t2查询的条件不一样,真的如同两张表,关联两张表,可以将两行或者多行数据合并成一行,不必使用wm_concat()函数。为了将t2表的数据全部查出来使用了右连接。
- 09-30SQL不重复查找数据及把一列多行内容拼成一行