如何将一个表中的一列数据作为PL/SQL中的不同列获取到另一张表中 [英] how to get one column data from one table into another table as different columns in PL/SQL

问题描述

我在名为

unsorted

---

Scott
100
10
Miller
200
20
Mike
300
30
Allen
400
4

现在，我希望以上数据在另一个由3列排序的表中

Now, I want the above data in another table sorted with 3 columns

name   sal   deptno

---

Scott  100   10
Miller 200   20
Mike   300   30
Allen  400   40

我正在处理PL/SQL查询，但要检索我想在每一列中给出的内容

I am processing a PL/SQL query but to retrieve I want to give in each column

我尝试了不同的方法，但是显示了一些错误.

I tried with different methodologies but showing some errors.

DECLARE 
  CURSOR raw_cur IS 
     SELECT raw_data 
       from table_a; 
  raw_rec raw_cur%ROWTYPE; 
BEGIN 
  OPEN raw_cur; 
  LOOP 
    FETCH raw_cur INTO raw_rec; 
    EXIT WHEN raw_cur%NOTFOUND; 
    INSERT INTO Temp_process(Name ,Sal , Deptno) 
      VALUES(raw_rec.Name, raw_rec.Sal, raw_rec.Deptno); 
  END LOOP; 
  COMMIT; 
  CLOSE raw_cur; 
END;

推荐答案

如果您保证源表中的数据按此特定顺序排列，则可以编写类似的查询来获得所需的结果:

If you guarantee that the data in you source table will be in this particular order you can write a similar query to achieve desired result:

-- sample of data from your question
SQL> with t1(col) as(
  2    select 'Scott'  from dual union all
  3    select '100'    from dual union all
  4    select '10'     from dual union all
  5    select 'Miller' from dual union all
  6    select '200'    from dual union all
  7    select '20'     from dual union all
  8    select 'Mike'   from dual union all
  9    select '300'    from dual union all
 10    select '30'     from dual union all
 11    select 'Allen'  from dual union all
 12    select '400'    from dual union all
 13    select '40'     from dual
 14  ) -- the query
 15  select max(decode(mod(rownum - 1, 3), 0, col)) as name
 16       , max(decode(mod(rownum - 1, 3), 1, col)) as sal
 17       , max(decode(mod(rownum - 1, 3), 2, col)) as depno
 18    from t1
 19    group by trunc((rownum -1)/ 3)
 20  /

NAME   SAL    DEPNO
------ ------ ------
Miller 200    20
Mike   300    30
Allen  400    40
Scott  100    10

---

附录

您不需要为此使用游标(除非这是某种特殊要求，要求使用游标).要用数据填充其他表，您只需使用INSERT INTO ... SELECT:

You do not need a cursor for this (unless it's some kind of special requirement that demands use of cursors). To populate other table with data you simply could use INSERT INTO ... SELECT:

insert into temp_process(name, sal, depno)
   select max(decode(mod(rownum - 1, 3), 0, col)) 
        , max(decode(mod(rownum - 1, 3), 1, col)) 
        , max(decode(mod(rownum - 1, 3), 2, col))
     from table_a
     group by trunc((rownum -1)/ 3)  

这篇关于如何将一个表中的一列数据作为PL/SQL中的不同列获取到另一张表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！