[SQL] 오라클 10g에 피벗

오라클 10g에 피벗

나는 오라클 10g를 사용하고 있습니다. 나는 임시 테이블 TEMP 있습니다.

TEMP는 다음과 같은 구조를 가지고 : -

USER COUNT TYPE
---- ----- ----
   1    10   T1
   2    21   T2
   3    45   T1
   1     7   T1
   2     1   T3

나는 모든 종류의 표시 열 이름을 가지고 있으며, 종류는 T1이, T2는 .. 테네시와 결과가 같을 것 같은 값을 가질 수하는 쿼리가 필요합니다 -

USER T1 T2 T3
---- -- -- --
   1 17  0  0
   2  0 21  1
   3 45  0  0

및 사용자 열은 유형의 총 개수를 표시합니다 모든 사용자와 T1, T2 열을 표시합니다.

해결법

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

   1.오라클 10g에서는이 더 PIVOT 기능 없었다하지만 당신은 CASE와 총을 사용하여 복제 할 수 있습니다 :

   오라클 10g에서는이 더 PIVOT 기능 없었다하지만 당신은 CASE와 총을 사용하여 복제 할 수 있습니다 :

   select usr,
     sum(case when tp ='T1' then cnt else 0 end) T1,
     sum(case when tp ='T2' then cnt else 0 end) T2,
     sum(case when tp ='T3' then cnt else 0 end) T3
   from temp
   group by usr;
   

   데모와 SQL 바이올린을 참조하십시오

   오라클 11g +이 있다면 당신은 피벗 기능을 사용할 수 있습니다 :

   select *
   from temp
   pivot
   (
     sum(cnt)
     for tp in ('T1', 'T2', 'T3')
   ) piv
   

   데모와 SQL 바이올린을 참조하십시오

   당신은 변환 할 값의 알 수없는 번호가있는 경우, 당신은이의 동적 버전을 생성하는 프로 시저를 만들 수 있습니다 :

   CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
   as
       sql_query varchar2(1000) := 'select usr ';
   
       begin
           for x in (select distinct tp from temp order by 1)
           loop
               sql_query := sql_query ||
                 ' , sum(case when tp = '''||x.tp||''' then cnt else 0 end) as '||x.tp;
   
                   dbms_output.put_line(sql_query);
           end loop;
   
           sql_query := sql_query || ' from temp group by usr';
   
           open p_cursor for sql_query;
       end;
   /
   

   다음 코드를 실행합니다 :

   variable x refcursor
   exec dynamic_pivot(:x)
   print x
   

   모든 버전에 대한 결과는 동일 :

   | USR | T1 | T2 | T3 |
   ----------------------
   |   1 | 17 |  0 |  0 |
   |   2 |  0 | 21 |  1 |
   |   3 | 45 |  0 |  0 |
   

   편집 : 당신이 전체 필드를 원하는 경우 귀하의 의견을 바탕으로, 가장 쉬운 방법은이 또 다른 SELECT 유사한의 질의 내부를 배치하는 것입니다 :

   select usr,
     T1 + T2 + T3 as Total,
     T1,
     T2,
     T3
   from
   (
     select usr,
       sum(case when tp ='T1' then cnt else 0 end) T1,
       sum(case when tp ='T2' then cnt else 0 end) T2,
       sum(case when tp ='T3' then cnt else 0 end) T3
     from temp
     group by usr
   ) src;
   

   데모와 SQL 바이올린을 참조하십시오

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

   2.여기에 테이블 작성에 대한 코드는 다음과 같습니다

   여기에 테이블 작성에 대한 코드는 다음과 같습니다

   CREATE TABLE TBL_TEMP
   (
       USR   NUMBER
      ,CNT   NUMBER
      ,TP    VARCHAR2 (10)
   );
   
   INSERT INTO TBL_TEMP VALUES (1,10,'T1');
   INSERT INTO TBL_TEMP  VALUES (2,21,'T2');
   INSERT INTO TBL_TEMP  VALUES (3,45,'T1');
   INSERT INTO TBL_TEMP  VALUES (1,7,'T1');
   INSERT INTO TBL_TEMP  VALUES (2,1,'T3');
   

   그리고, 여기에 귀하의 요청에 대한 코드는 다음과 같습니다

     SELECT T1.USR
           ,SUM (T1) T1
           ,SUM (T2) T2
           ,SUM (T3) T3
       FROM (SELECT DISTINCT USR FROM TBL_TEMP) T1
           ,(SELECT T2.USR
                   ,DECODE (T2.TP, 'T1', T2.CNT, 0) T1
                   ,DECODE (T2.TP, 'T2', T2.CNT, 0) T2
                   ,DECODE (T2.TP, 'T3', T2.CNT, 0) T3
               FROM TBL_TEMP T2) T2
      WHERE T1.USR = T2.USR
   GROUP BY T1.USR;
   

   그리고, 결과는 당신이 원하는 것입니다.

   여기에 SQL 바이올린 링크에서 그것을 확인

3. ==============================

   3.쿼리를 확인하시기 바랍니다 :

   쿼리를 확인하시기 바랍니다 :

   SET SERVEROUTPUT ON;
   DECLARE
   CURSOR V_CUR IS
    SELECT DISTINCT "TYPE" FROM temp_tbl;
   
   V_QUERY CLOB;
   V_COLUMNHEADING CLOB;
   V_ROW V_CUR%ROWTYPE;
   BEGIN
     OPEN V_CUR;
     LOOP
     FETCH V_CUR INTO V_ROW;
     EXIT WHEN V_CUR%NOTFOUND;
       V_COLUMNHEADING:=CONCAT(V_COLUMNHEADING,''''||V_ROW."TYPE"||''' AS "'||V_ROW."TYPE"||'",');
     END LOOP;
   
     IF NVL(V_COLUMNHEADING,' ') <> ' ' THEN
           V_COLUMNHEADING := SUBSTR(V_COLUMNHEADING,0,LENGTH(V_COLUMNHEADING)-1);
     END IF;
   
     V_QUERY := CONCAT(V_QUERY,'select * from (select * from temp_tbl)a pivot (sum("COUNT")for "TYPE" in ('||V_COLUMNHEADING||'))b;');
     DBMS_OUTPUT.PUT_LINE(V_QUERY);
   END ;
   

   사용 문 OPEN CUR_TURN_TIME에 대한 V_QUERY 데이터를 선택합니다; DBMS_OUTPUT.PUT_LINE (V_QUERY); 여기서 CUR_TURN_TIME 인 커서 대신 패키지에 선언했다.

from https://stackoverflow.com/questions/13410464/pivot-on-oracle-10g by cc-by-sa and MIT license