Question

oracle怎么把一个字段根据不同的条件拆分成多列展现

表A
name type
1 A
1 B
1 A
1 C

拆分后变成
name1 type1 name2 type2 name3 type3
1 A 1 B 1 C
1 A

Answer 1

with t as (select a.name, a.type, row_number() over(partition by type order by name) r from a)
select max(case when type='A' then name end) name1, 'A' type1,
max(case when type='B' then name end) name2, 'B' type2,
max(case when type='C' then name end) name3, 'C' type3
from t
group by r

追问

ORA-32039：递归WITH子句必须具有列别名列表
报错呀

追答

没报错呀
with a as (select 1 name, 'A' type from dual
union all select 1, 'B' from dual
union all select 1, 'A' from dual
union all select 1, 'C' from dual),
t as (select a.name, a.type, row_number() over(partition by type order by name) r from a)
select max(case when type='A' then name end) name1, max(case when type='A' then type end) type1,
max(case when type='B' then name end) name2, max(case when type='B' then type end) type2,
max(case when type='C' then name end) name3, max(case when type='C' then type end) type3
from t
group by r
哪来的递归？

Answer 2

你直接加 where 条件不就可以啦 ：form 表A where type ="A"

追问

那只查了一列，其余的几列怎么办

Answer 3

用decode函数可以

追问

怎么弄呢？

Answer 4

你的with t（列名）as

Answer 5

想到了行转列PIVOT函数。但是你这个需求，我做不出来。