oracle:动态列名 [英] oracle : Dynamic column name

问题描述

可能重复:
Oracle行到列的转换

Possible Duplicate:
Oracle Rows to Column Transformation

我真正的问题就是这样，但是有很多相互关联的表.因此，我只是创建了一个示例，因此假设此问题得以解决，那么我的实际问题就得到了解决. 这是我的桌子:

My real problem is just like this but has many tables related to each other. So, I just created a example, so assuming if this gets solved, my real problem gets solved. Here are my tables :

tbl_products

    tp_id | tp_name 

     1      apple

     2      mango

     3      pineapple

tbl_sales

    ts_id | ts_location | ts_tp_id | ts_sales

       1        NY              2           5
       2        LN              2           10  
       3        QL              1           25          
       4        QL              3           20
       5        LN              3           35
       6        NY              3           50
       7        NY              1           100

如果我有tbl_products和tbl_sales这两个表，如何创建具有这样的动态列的查询:

If I have these two tables, tbl_products and tbl_sales, how to create a query that has a dynamic columns like this :

 sales_location | apple | mango | pineapple 
      NY           100       5        50

根据位置，苹果，芒果和菠萝应具有的销售总数

where apple, mango and pineapple should have the total number of sales according to location

推荐答案

由于您使用的是Oracle10g，因此没有PIVOT函数，因此您将必须使用带有CASE语句的聚合函数来执行这种类型的转换

Since you are using Oracle10g, there is no PIVOT function so you will have to perform this type of transformation using an aggregate function with a CASE statement.

如果提前知道这些值，则可以在静态版本中对其进行硬编码:

If the values are known ahead of time, then you can hard code them in a static version:

select s.ts_location,
  sum(case when p.tp_name = 'apple' then s.ts_sales else 0 end) Apple,
  sum(case when p.tp_name = 'mango' then s.ts_sales else 0 end) Mango,
  sum(case when p.tp_name = 'pineapple' then s.ts_sales else 0 end) Pineapple
from tbl_sales s
inner join tbl_products p
  on s.ts_tp_id = p.tp_id
group by s.ts_location

请参见带有演示的SQL提琴

但是，如果您提前不知道您的值，那么您必须实现动态sql，并且在Oracle中，您将需要使用以下过程:

But if you values are not known ahead of time, then you have to implement dynamic sql and in Oracle you will want to use a procedure for this:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select s.ts_location ';

    begin
        for x in (select distinct tp_name from tbl_products order by 1)
        loop
            sql_query := sql_query ||
                ' , sum(case when p.tp_name = '''||x.tp_name||''' then s.ts_sales end) as '||x.tp_name;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from tbl_sales s 
                                                inner join tbl_products p
                                                  on s.ts_tp_id = p.tp_id
                                                group by s.ts_location';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

然后返回可以使用的结果(注意:这是我在Toad中所做的事情):

Then to return the results you can use (note: this is how I do it in Toad):

variable x refcursor
exec dynamic_pivot(:x)
print x

两者都会返回结果:

| TS_LOCATION | APPLE | MANGO | PINEAPPLE |
-------------------------------------------
|          LN |     0 |    10 |        35 |
|          QL |    25 |     0 |        20 |
|          NY |   100 |     5 |        50 |

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