固定的话用:with t as
(select 220000010000277 as facctcode, '负债类' as facctclass
from dual
union all
select 104150002 as facctcode, '资产类' as facctclass from dual
)select max(decode(facctcode, 220000010000277, facctcode, null)) as col1,
max(decode(facctcode, 220000010000277, facctclass, null)) as col2,
max(decode(facctcode, 104150002, facctcode, null)) as col3,
max(decode(facctcode, 104150002, facctclass, null)) as col4
from t;
动态的话,推荐外部程序实现。
(select 220000010000277 as facctcode, '负债类' as facctclass
from dual
union all
select 104150002 as facctcode, '资产类' as facctclass from dual
)select max(decode(facctcode, 220000010000277, facctcode, null)) as col1,
max(decode(facctcode, 220000010000277, facctclass, null)) as col2,
max(decode(facctcode, 104150002, facctcode, null)) as col3,
max(decode(facctcode, 104150002, facctclass, null)) as col4
from t;
动态的话,推荐外部程序实现。
解决方案 »
- 错误ORA-06512的问题
- 各位大侠,帮个忙!
- 100分只求只有一个表的SQL语句
- oracle SQL*Plus工具
- oracle数据库中的表被truncate掉了,怎么恢复?
- Oracle触发器的几个应用(有高手吗?)
- 在笔记本(p4,512m内存,40g硬盘)上装载window2000,ORACLE8i,php,apache最多能带几台客户端
- Oracle中执行procedure慢!oracle参数该怎么设?
- 特急问题:高手帮忙呀!请看触发输出!!!!
- 请问在PL/SQL存储过程中,我定义了一个数组但不知怎么访问这个数组,用操作符[]竟然说语法错误,谁能解决。
- oracle中 用户和数据库的关系
- 数据同步的问题(非实时)
WITH T AS
(SELECT 220000010000277 AS FACCTCODE, '负债类' AS FACCTCLASS
FROM DUAL
UNION ALL
SELECT 104150002 AS FACCTCODE, '资产类' AS FACCTCLASS FROM DUAL)
SELECT LISTAGG(T.FACCTCODE || ' ' || FACCTCLASS, ' ') WITHIN GROUP(ORDER BY FACCTCODE)
FROM T;
with t as
(select 220000010000277 as facctcode, 'fuzai' as facctclass
from dual
union all
select 104150002 as facctcode, 'zichan' as facctclass from dual
UNION ALL
select 13212121 as facctcode, 'zichan' as facctclass from dual
)
SELECT replace(ltrim(max(SYS_CONNECT_BY_PATH(t1.facctcode||' '||t1.facctclass, ',')), ','),',',' ') col
FROM(SELECT t.*,ROWNUM rn FROM t) t1
START WITH t1.rn=1
CONNECT BY PRIOR t1.rn=t1.rn-1;
如果在意里面的“,” 可以用replace('str',',',' ')函数替换。
要是在类型的数目不确定的情况下,没人这么作行转列的,这个设计到数据库设计的问题了
oracle table 能支持这么多的列么?