oracle:动态列名

本文介绍了oracle:动态列名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

可能重复: 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 |