在Oracle 11g中转置查询结果 [英] Transpose Query Result in Oracle 11g

问题描述

我有一个查询，该查询以以下形式返回结果:

I have a query which returns out result in following Form:

Element  READING1  READING2 READING3
---------------------------------------
1        0.25      1.5      3.5
2        1.3       2.3      5.5
3        4.5       5.5      4.3
..        ..       ..       ..   
n        1.5       2.3      5.5
---------------------------------------

我想要以以下形式输出:

I want output in following form:

           1        2          3    ..     n
         ---------------------------------------
READING1  0.25     1.3        4.5   ..    1.5
READING2  1.5      2.3        5.5   ..    2.3
READING3  3.5      5.5        4.3   ..    5.5

即我必须转置桌子. 我尝试通过以下方式使用Oracle Pivot:

i.e I have to transpose the table. I have tried using Oracle Pivot with following way:

WITH T AS (
SELECT  Element,READING1 from ZZZ;    ----(1)
)
SELECT * FROM T
PIVOT( MAX(READING1) FOR ELEMENT IN (1,2,3,..n))     ----(2)

这仅给我提供READING1的结果，但是我无法正确生成所有读数的结果.任何帮助将不胜感激.

This gives me result only for READING1,however I am unable to produce result for all readings correctly. Any help would be highly appreciated.

预先感谢

最好的问候， 库纳尔

推荐答案

您已经很接近了-您想要的是UNPIVOT和PIVOT的组合:

You're close - what you want is a combination of UNPIVOT and PIVOT:

with T AS (
  select 1 as element, 1.1 as reading1, 1.2 as reading2, 1.3 as reading3 from dual union all
  select 2 as element, 2.1 as reading1, 2.2 as reading2, 2.3 as reading3 from dual union all
  select 3 as element, 3.1 as reading1, 3.2 as reading2, 3.3 as reading3 from dual 
)
select * from (
  select * from t
  unpivot (reading_value
    for reading_name in ("READING1", "READING2", "READING3")
    )
  pivot(max(reading_value) for element in (1,2,3)
  )
)
order by reading_name

此查询

• 将列的读数1，读数2，读数3转换为单独的行(名称进入 reading_name ，将值转换为 reading_value );这使我们每个(element，reading_name)一行
• 将行 1、2 *，3( element 的值)转换为列"1"，"2"，"3"；这给我们每个读数名称一行

• converts the columns reading1, reading2, reading3 into separate rows (the name goes into reading_name, the value into reading_value); this gives us one row per (element,reading_name)
• converts the rows 1, 2*, 3 (the values for element) into columns '1', '2', '3'; this gives us one row per reading_name

更新

如果直到运行时才知道元素列表(例如，因为用户可以选择它们)，则需要一种更加动态的方法.这是一种为给定的元素列表动态创建SQL语句并为结果集使用sys_refcursor的解决方案.

If the list of elements is not know until run time (e.g. because the user has the option of selecting them), you need a more dynamic approach. Here's one solution that dynamically creates a SQL statement for the given list of elements and uses a sys_refcursor for the result set.

-- setup table
create table T AS 
  select 1 as element, 1.1 as reading1, 1.2 as reading2, 1.3 as reading3 from dual union all
  select 2 as element, 2.1 as reading1, 2.2 as reading2, 2.3 as reading3 from dual union all
  select 3 as element, 3.1 as reading1, 3.2 as reading2, 3.3 as reading3 from dual ;  
/

declare
  l_Elements dbms_sql.Number_Table;

  function pivot_it(p_Elements in dbms_sql.Number_Table) 
    return sys_refcursor is
      l_SQL CLOB := empty_clob();
      l_Result sys_refcursor;
    begin
      l_SQL := '
        select * from (
          select * from t 
            unpivot (reading_value
              for reading_name in ("READING1", "READING2", "READING3")
            )
          pivot(max(reading_value) for element in (';
      for i in 1 .. p_Elements.count
              loop
                  l_SQL := l_SQL || to_char(p_Elements(i)) || ',';
                end loop;
      -- remove trailing ','                
      l_SQL := regexp_replace(l_SQL, ',$');                
      l_SQL := l_SQL || ')
        )
      )';
      dbms_output.put_line(l_SQL);
      open l_Result for l_SQL;
      return l_Result;
  end;      
begin
  l_Elements(1) := 1;
  l_Elements(2) := 2;
  -- uncomment this line to get all 3 elements
  -- l_Elements(3) := 3;
  -- return the cursor into a bind variable (to be used in the host environment)
  :p_Cursor := pivot_it(l_Elements);  
end;

如何使用从此函数返回的游标取决于您所使用的环境-在SQL/Plus中，您仅可以打印它，并且大多数编程语言的Oracle绑定都开箱即用地支持它.

How you use the cursor returned from this function depends on the environment you're using - in SQL/Plus you can just print it, and most programming languages' Oracle bindings support it out-of-the-box.

注意:尽管此代码适用于所提供的数据，但它甚至缺少基本的错误检查.这尤其重要，因为动态SQL始终是SQL注入攻击的可能目标.

CAVEAT: While this code works for the data provided, it lacks even basic error checking. This is especially important because dynamic SQL is always a possible target for SQL injection attacks.

这篇关于在Oracle 11g中转置查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！