[SQL] 오라클 동적 피벗

오라클 동적 피벗

나는 아래 표가 있습니다. 나는 열 CCL 기반으로 열을 작성해야합니다. 열 CCL의 값을 알 수 있습니다. 여기 시작하는 곳없는 확신 해요. 어떤 도움을 주시면 감사하겠습니다.

TABLEA

ID    CCL    Flag
1     john     x
1     adam     x
1     terry
1     rob      x
2     john     x

질문:

SELECT *
FROM TABLEA

산출:

ID  John  Adam  Terry  Rob
 1    x     x           x
 2    x       

해결법

1. ==============================

   1.열이 실행시 알 수없는 결과에 대한 동적 SQL을 사용하면 특정 다른 RDMBS에 비해 오라클의 번거 로움 비트입니다.

   열이 실행시 알 수없는 결과에 대한 동적 SQL을 사용하면 특정 다른 RDMBS에 비해 오라클의 번거 로움 비트입니다.

   출력 형식에 대한 레코드는 아직 알 수 없기 때문에, 이는 미리 정의 될 수 없다.

   오라클 11g에서는 하나의 방법은 피봇 결과를 임시 테이블을 생성하는 무명 절차를 사용하는 것입니다.

   그런 다음 임시 테이블에서 결과를 선택합니다.

   declare
     v_sqlqry clob;
     v_cols clob;
   begin
     -- Generating a string with a list of the unique names
     select listagg(''''||CCL||''' as "'||CCL||'"', ', ') within group (order by CCL)
     into v_cols
     from 
     (
       select distinct CCL
       from tableA
     );
   
     -- drop the temporary table if it exists
     EXECUTE IMMEDIATE 'DROP TABLE tmpPivotTableA';
     EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;
   
     -- A dynamic SQL to create a temporary table 
     -- based on the results of the pivot
     v_sqlqry := '
       CREATE GLOBAL TEMPORARY TABLE tmpPivotTableA
       ON COMMIT PRESERVE ROWS AS
       SELECT * 
       FROM (SELECT ID, CCL, Flag FROM TableA) src 
       PIVOT (MAX(Flag) FOR (CCL) IN ('||v_cols||')) pvt';
   
     -- dbms_output.Put_line(v_sqlqry); -- just to check how the sql looks like
     execute immediate v_sqlqry;
   
   end;
   /
   
   select * from tmpPivotTableA;
   

   보고:

   ID  adam john rob terry
   --  ---- ---- --- -----
   1   x    x    x
   2        x      
   

   당신은 DB <> 바이올린 여기에 테스트를 찾을 수 있습니다

   오라클 11g에서 사용되는 (안톤 페르 만든) 또 다른 멋진 트릭은이 블로그에서 찾아 볼 수 있습니다. 하지만 당신은 그것을 위해 피벗 기능을 추가해야합니다. 소스 코드는이 우편에서 찾을 수 있습니다

   는 SQL이 단순하게 할 수있는 후 :

   select * from 
   table(pivot('SELECT ID, CCL, Flag FROM TableA'));
   

   당신은 DB <> 바이올린 여기에서 테스트를 찾을 수 있습니다

2. ==============================

   2.오라클은 구문 분석 단계에서 선택 목록에있는 모든 열을 알고 있어야합니다.

   오라클은 구문 분석 단계에서 선택 목록에있는 모든 열을 알고 있어야합니다.

   이 결과 몇 가지가 있습니다

   "동적 피벗"를 다루는 최선의 선택 그래서 UI에서 피벗 및 형식 결과이다. 그러나 고려하는 것이 좋습니다 데이터베이스에 몇 가지 옵션이 여전히 존재한다.

   피벗 결과 XML을 생성하고 구문 분석.

   XML 다음 구문 분석 결과를 피벗 작업을 수행합니다. 이 경우, 결국, 당신은 피벗 열을 식 으로든를 지정해야합니다.

   create table tablea(id, ccl, flag) as
   (
     select 1, 'john', 'x' from dual
     union all select 1, 'adam', 'x' from dual
     union all select 1, 'terry', null from dual
     union all select 1, 'rob', 'x' from dual
     union all select 2, 'john', 'x' from dual
   );
   

   예를 들어 아래에서 당신은 CCL에 대한 값의 목록을 제공 할 필요가 없습니다, 사용자가 지정한 유일한 리터럴은 다음과 같습니다 : 피봇 (CCL)에 사용되는 피벗 식 (FLAG) 및 열.

   SQL> select id, x.*
     2  from tablea t
     3  pivot xml (max(flag) flag for ccl in(any))
     4  -- parsing output
     5  , xmltable('/PivotSet' passing ccl_xml
     6             columns
     7               name1 varchar2(30) path '/PivotSet/item[1]/column[@name="CCL"]/text()',
     8               value1 varchar2(30) path '/PivotSet/item[1]/column[@name="FLAG"]/text()',
     9               name2 varchar2(30) path '/PivotSet/item[2]/column[@name="CCL"]/text()',
    10               value2 varchar2(30) path '/PivotSet/item[2]/column[@name="FLAG"]/text()',
    11               name3 varchar2(30) path '/PivotSet/item[3]/column[@name="CCL"]/text()',
    12               value3 varchar2(30) path '/PivotSet/item[3]/column[@name="FLAG"]/text()',
    13               name4 varchar2(30) path '/PivotSet/item[4]/column[@name="CCL"]/text()',
    14               value4 varchar2(30) path '/PivotSet/item[4]/column[@name="FLAG"]/text()') x;
   
           ID NAME1 VALUE NAME2 VALUE NAME3 VALUE NAME4 VALUE
   ---------- ----- ----- ----- ----- ----- ----- ----- -----
            1 adam  x     john  x     rob   x     terry
            2 john  x
   

   당신은이 개 중요한 세부 사항을 발견했을 수 있습니다

   이 같은 출력을 얻을 만 인덱스를 사용하는 것이 가능하다.

   select id, x.*
   from tablea
   pivot xml (max(flag) flag for ccl in(any))
   -- parsing output
   , xmltable('/PivotSet' passing ccl_xml
              columns
                name1 varchar2(30) path '/PivotSet/item[1]/column[1]',
                value1 varchar2(30) path '/PivotSet/item[1]/column[2]',
                name2 varchar2(30) path '/PivotSet/item[2]/column[1]',
                value2 varchar2(30) path '/PivotSet/item[2]/column[2]',
                name3 varchar2(30) path '/PivotSet/item[3]/column[1]',
                value3 varchar2(30) path '/PivotSet/item[3]/column[2]',
                name4 varchar2(30) path '/PivotSet/item[4]/column[1]',
                value4 varchar2(30) path '/PivotSet/item[4]/column[2]') x;
   

   하지만 여전히 출력의 각 피벗 열의 두 개의 열이 있습니다.

   당신의 예에서와 같이 쿼리 반환 다음은 정확히 같은 데이터

   SQL> select id, x.*
     2  from tablea
     3  pivot xml (max(flag) flag for ccl in(any))
     4  -- parsing output
     5  , xmltable('/PivotSet' passing ccl_xml
     6             columns
     7               john varchar2(30) path '/PivotSet/item[column="john"]/column[2]',
     8               adam varchar2(30) path '/PivotSet/item[column="adam"]/column[2]',
     9               terry varchar2(30) path '/PivotSet/item[column="terry"]/column[2]',
    10               rob varchar2(30) path '/PivotSet/item[column="rob"]/column[2]') x;
   
           ID JOHN  ADAM  TERRY ROB
   ---------- ----- ----- ----- -----
            1 x     x           x
            2 x
   

   그러나 기대 ... CCL에 대한 모든 값은 쿼리에 지정됩니다. 열 캡션이 테이블의 데이터에 의존 할 수 없기 때문입니다. 그래서 당신은 그냥 같은 성공 절에 대한 모든 값을 하드 코딩 한 수 있다면 XML에 대한 피벗에있는 점은 무엇인가? 아이디어의 하나는 오라클 SQL 엔진 전치의 원인이 질의하고있는 디스플레이 출력이 도구는 단지 제대로 구문 분석 XML을 가지고 있다는 것입니다. 그래서 당신은 두 개의 층으로 논리를 선회 분할합니다. XML 구문 분석 응용 프로그램에서 말하자면, 외부 SQL을 수행 할 수 있습니다.

   ODCI 테이블 인터페이스

   안톤의 솔루션에 다른 답변에 링크가 이미 있습니다. 또한 여기에 예를 확인할 수 있습니다. 그리고, 물론, 그것의 오라클 문서에 자세히 설명했다.

   다형성 테이블 함수

   다형성 테이블 함수 - 하나 개 더 진보 된 기술은 오라클 18에서 소개하고있다. 그러나 다시, 당신은 당신이 CCL에 새로운 가치를 추가 한 후 쿼리의 열 목록이 변경됩니다 기대는 안된다. 그것은 단지 재 해석 한 후 변경할 수 있습니다. 이 각각의 큐션 전에 하드 구문 분석을 강제 할 수있는 방법이 있지만, 그것은 또 다른 주제입니다.

   동적 SQL

   또한 이미 의견에서 지적 마지막으로, 당신은 좋은 오래된 DSQL를 사용할 수 있습니다. 첫 번째 단계는 - 테이블의 내용을 기반으로 SQL 문을 생성합니다. 두 번째 단계 -를 실행합니다.

   SQL> var rc refcursor
   SQL> declare
     2    tmp clob;
     3    sql_str clob := 'select * from tablea pivot (max(flag) for ccl in ([dynamic_list]))';
     4  begin
     5    select listagg('''' || ccl || ''' as ' || ccl, ',') within group(order by max(ccl))
     6      into tmp
     7      from tablea
     8     group by ccl;
     9    open :rc for replace(sql_str, '[dynamic_list]', tmp);
    10  end;
    11  /
   
   PL/SQL procedure successfully completed.
   
   SQL> print rc
   
           ID ADAM  JOHN  ROB   TERRY
   ---------- ----- ----- ----- -----
            1 x     x     x
            2       x
   

from https://stackoverflow.com/questions/50259728/oracle-dynamic-pivoting by cc-by-sa and MIT license