Oracle数据库与动态数据 [英] Oracle pivot with dynamic data

问题描述

我是oracle数据库的新手，我正在尝试使用PIVOT将行转换成列。我有以下表格..

I am new to the oracle database and I am trying to use PIVOT to convert rows into columns. I have following tables..

table 1
solid       solname
--------------
1        xxxxxx
2        yyyyyyy
table2
id      name           abbrv 
----------------------------------
1        test db          tdb
2        Prdocuiton db     pdb

table3
id     solId
-------------
1   1
1   2
1   3
1   4
1   5
1   7
1   8
1   9
1   22
1   23
1   24
1   25
2   26
2   27
1   28
1   29
1   32
1   33
1   34
1   35
1   36
1   37
3   38
1   39
1   40
1   43
1   44

表3是表1和表3的映射表。

table 3 is mapper table for table 1 and table 3.

我需要创建一个视图，列表中的列和每个列的额外列solname的。所以视图看起来像

I need to create a view with the columns in table2 and extra column for each solname's. So the view looks like

id      name           abbrv   xxxxxxx    yyyyyyy
--------------------------------------------------

有没有办法在oracle数据库中使用PIVOT？

So is there a way to do this using PIVOT in oracle database?

推荐答案

对于动态SQL数据透视，您需要执行类似的操作：

For Dynamic SQL Pivoting you need to do something similar :

create or replace view sol_view
as
select 
    t1.solname, 
    t2.name, 
    count(t3.abbrv),
from 
    table1 t1, 
    table2 t2, 
    table3 t3
where 
    t1.solid = t3.solid 
    and t2.id = t3.id
group by
    t1.solname,
    t3.name

select * from table( pivot('select * from sol_view') )

警告：我从来没有尝试过这个，但是从这里了解了逻辑：
http：// techn ology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

Caveat: I have never tried this but understood the logic from here: http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

对于静态SQL数据透视，尝试一下大致沿着这些线。没有尝试过或测试过：

For Static SQL Pivoting, try something roughly along these lines. Never tried or tested though:

with pivot_data as (
    select t1.solname, t2.name, t3.abbrv
from table1 t1, table2 t2, table3 t3
where t1.solid = t3.solid 
and t2.id = t3.id
)
select * 
from pivot_data
pivot ( 
    count(abbrv) 
    for solname 
    in ('xxxxxx','yyyyyyy') 
);

这篇关于Oracle数据库与动态数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！