把一个表的列值转为显示的列标题,如何实现

iloveora

我用这种方式试验过,完全正确的,就是有一个小问题
这样插入到table_b里,结果将是
type err1 err2 right
1     15   0      0
1     0     28    0
2    12    0      0
2    0      0      5
3    0     10     0
而不是我想要的把type=1的列变为一行
type err1 err2 right
1 15 28 0
2 12 0 5
3 0 10 0

我原来的做法是用建表table_b的方法再建一个表table_c
然后          insert into table_c (type,err1,err2,right)
                 select type,nvl(max(err1),0),nvl(max(err2),0),nvl(max(right),0)  from table_b group by type
但这样做显然不是动态实现的,很不好,我也想不出别的好办法,动态sql实在是不熟

最初由 rollingpig 发布
[B]This one will better
[php]
create table table_b (type number)
/
begin
for a in (select distinct status from table_a ) loop
        execute immediate
                'alter table table_b add ( '||a.status ||' number default 0)';
end loop;
for a in (select type,status,amount from table_a ) loop
        execute immediate
                'insert into table_b (type,'||a.status||')
                values(:1,:2)' using a.type,a.amount;
end loop;
end ;
/
[/php] [/B]

iloveora

jaunt版主给的两个链接实在没看懂,水平未到...尤其是老外提供的那个语句...

jaunt

最初由 iloveora 发布
[B]我用这种方式试验过,完全正确的,就是有一个小问题
这样插入到table_b里,结果将是
type err1 err2 right
1     15   0      0
1     0     28    0
2    12    0      0
2    0      0      5
3    0     10     0
而不是我想要的把type=1的列变为一行
type err1 err2 right
1 15 28 0
2 12 0 5
3 0 10 0

我原来的做法是用建表table_b的方法再建一个表table_c
然后          insert into table_c (type,err1,err2,right)
                 select type,nvl(max(err1),0),nvl(max(err2),0),nvl(max(right),0)  from table_b group by type
但这样做显然不是动态实现的,很不好,我也想不出别的好办法,动态sql实在是不熟


[/B]

在
type err1 err2 right
1     15   0      0
1     0     28    0
2    12    0      0
2    0      0      5
3    0     10     0
的基础上，再：
select type, sum(err1) as err1, sum(err2) as err2, sum(right) as right
from table_b
group by type
到是可以得到你想要的结果。

wyq21973

如果table_b允许drop的话，只要构建两个动态SQL就行了：
第一个：
Exceute immediate 'Drop table table_b';
第二个：
Execute immediate 'Create table table_b (...) Select ...';

jaunt

最初由 iloveora 发布
[B]jaunt版主给的两个链接实在没看懂,水平未到...尤其是老外提供的那个语句... [/B]

方法一（By nyfor）：
create or replace type strings_table is table of varchar2(20);
/
create or replace function merge (pv in strings_table) return varchar2
is
  ls varchar2(4000);
begin
  for i in 1..pv.count loop
    ls := ls || pv(i);
  end loop;
  return ls;
end;
/
create table t (id number,name varchar2(10));
insert into t values(1,'Joan');
insert into t values(1,'Jack');
insert into t values(1,'Tom');
insert into t values(2,'Rose');
insert into t values(2,'Jenny');

select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names
from (select distinct id from t) t0;

自己写的函数merge，主要作用就是把一个竖表变横表，函数很简单，就是使用了一个循环而已。

估计你是cast( multiset (...) ) 这一部分难以理解。
CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.
http://oraclesvca2.oracle.com/do ... 40/functions15a.htm

后面这个SQL中，t0 得到一个id 的列表，且每个id 只有一条记录。
子查询得到这个id 所对应的所有name，然后通过cast(multiset(...)) 把这个id 对应所有name 转换成自定义类型，然后再通过自己写的函数merge 将这个自定义类型由竖表转换成横表。

jaunt

最初由 iloveora 发布
[B]jaunt版主给的两个链接实在没看懂,水平未到...尤其是老外提供的那个语句... [/B]

老外这个主要是lead 的使用。
LEAD is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LEAD provides access to a row at a given physical offset beyond that position.

http://download-west.oracle.com/ ... s60a.htm#SQLRF00656

老外这个的优点是只用了一个SQL，但是不足是必须事先知道同一个type 下最多有多少个status。

rollingpig

try this :
[php]
begin
execute immediate
        'drop table table_b';
execute immediate
        'create table table_b (type number)';
for a in (select distinct status from table_a ) loop
        execute immediate
                'alter table table_b add ( '||a.status ||' number default 0)';
end loop;
for a in (select type,status,amount from table_a ) loop
        execute immediate
                'merge into table_b t_b using
                        (select :1 as type,:2 as amount from dual ) t_a
                on (t_b.type = t_a.type )
                when matched then
                        update set t_b.'||a.status||'=t_a.amount+t_b.'||a.status||'
                when not matched then
                        insert (type,'||a.status||') values(t_a.type,t_a.amount)'
        using a.type,a.amount;
end loop;
end ;
/

[/php]

iloveora

谢谢各位!昨天一直忙于工作,没有时间回复
我试图用update if SQL%NOTFOUND then insert来做
就是第二个for in里把insert换成上面步骤
修改rollingpig的语句如下:

create table table_b (type number)
/
begin
for a in (select distinct status from table_a ) loop
        execute immediate
                'alter table table_b add ( '||a.status ||' number default 0)';
end loop;
for a in (select type,status,amount from table_a ) loop
        execute immediate
                'update table_b set '||a.status||'=:1 where type=a.type';
       if SQL%NOTFOUND then
        excute immediate        
               'insert into table_b (type,'||a.status||')
                values(:1,:2)' using a.type,a.amount;
end loop;
end ;

但这样做老是报错,说update那句column定义错误,a.type
我对动态sql实在不熟,这句话for a in(select type,status,amount from table_a) 即后面的引用a.mount我不明白这种是什么用法

jaunt

update 那句可以写成（你table_a 的type 字段和amount 都是数字型的话）：

execute immediate
'update table_b set '||a.status||'=nvl ( '||a.status||' , 0 ) + '|| to_char(a.amount) ||' where type=' || to_char(a.type);

husthxd

看看下面的语句能否满足你的需求?

SQL> create table test_1
  2  (type number,status varchar2(20),amout number)
  3  /

表已创建。

SQL> insert into test_1
  2  values(1,'err1',15)
  3  /

已创建 1 行。

SQL> insert into test_1
  2  values(1,'err2',28)
  3  /

已创建 1 行。

SQL> insert into test_1
  2  values(2,'err1',12)
  3  /

已创建 1 行。

SQL> insert into test_1
  2  values(2,'right',5)
  3  /

已创建 1 行。

SQL> insert into test_1
  2  values(3,'err2',10)
  3  /

已创建 1 行。

SQL> commit
  2  /

提交完成。

SQL>
SQL> select type,
  2  sum(decode(status,'err1',amout,0)) err1,
  3  sum(decode(status,'err2',amout,0)) err2,
  4  sum(decode(status,'right',amout,0)) right
  5  from test_1
  6  group by type
  7  /

      TYPE       ERR1       ERR2      RIGHT
---------- ---------- ---------- ----------
         1         15         28          0
         2         12          0          5
         3          0         10          0

SQL>
SQL>