복붙노트

[SQL] 오라클 SQL 동적 피벗

SQL

오라클 SQL 동적 피벗

... 피봇 (B에 대한 합계 (A) (X))

이제 B는 데이터 유형 VARCHAR2이며 X 쉼표로 구분 VARCHAR2 값의 문자열이다. X 값은 동일한 테이블의 칼럼 (CL을 말한다)으로부터 선택하여 고유 값이다. 이 방법 피벗 쿼리가 작동했다.

그러나 문제는 열 CL I에 새 값이있을 때마다 수동으로 문자열 X.에 있음을 추가해야한다는 것입니다

나는 CL에서 선택 고유 값과 X를 교체했습니다. 그러나 쿼리가 실행되고 있지 않습니다. 내가 느낀 이유는 X를 교체하기 위해 우리가 값을 쉼표로 구분해야한다는 사실 때문이었다. 그리고 내가 문자열 X. 그러나 쿼리가 여전히 실행되지 않습니다으로 경기에 정확한 출력을 반환하는 함수를 만들었습니다. 도시 된 에러 메시지는 "파일의 통신 채널의 끝"등 등 "righr의 괄호 누락"처럼 나는 피벗 XML 대신 단지 피벗 쿼리 실행을 시도했지만 전혀 가치없는 oraxxx 등 같은 vlaues을 제공합니다.

어쩌면 내가 그것을 제대로 사용하고 있지 않다. 당신은 내게 동적 값으로 피벗을 만들 수있는 몇 가지 방법을 말할 수 있습니까?

해결법

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

    1.당신은 피벗 절의 IN 절에서 일정하지 않은 문자열을 넣을 수 없습니다. 당신은 그것을 위해 피벗 XML을 사용할 수 있습니다.

    당신은 피벗 절의 IN 절에서 일정하지 않은 문자열을 넣을 수 없습니다. 당신은 그것을 위해 피벗 XML을 사용할 수 있습니다.

    문서에서 :

    그것은 다음과 같이한다 :

    select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in(any)
) t;

    또한 대신 하위 쿼리를 가질 수 ANY 키워드 :

    select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;

    여기 sqlfiddle 데모입니다

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

    2.당신은 바람직 출력보다 약간 적은 출력 PIVOT의 XML을 사용하지 않고 PIVOT의 IN 문에서 동적 문을 넣을 수 없습니다. 그러나, 당신은 당신의 진술로의 IN 문자열 입력을 만들 수 있습니다.

    당신은 바람직 출력보다 약간 적은 출력 PIVOT의 XML을 사용하지 않고 PIVOT의 IN 문에서 동적 문을 넣을 수 없습니다. 그러나, 당신은 당신의 진술로의 IN 문자열 입력을 만들 수 있습니다.

    첫째, 여기 내 샘플 테이블;

      myNumber    myValue myLetter
---------- ---------- --------
         1          2 A        
         1          4 B        
         2          6 C        
         2          8 A        
         2         10 B        
         3         12 C        
         3         14 A

    먼저 설치 문자열은 IN 문에서 사용할 수 있습니다. 여기에서 "str_in_statement"로 문자열을 가하고 있습니다. 우리는 설정에 문자열을 열 NEW_VALUE 및 LISTAGG을 사용하고 있습니다.

    clear columns
COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT 
    LISTAGG('''' || myLetter || ''' AS ' || myLetter,',')
        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
    FROM (SELECT DISTINCT myLetter FROM myTable);

    귀하의 문자열은 같은 모양 :

    'A' AS A,'B' AS B,'C' AS C

    이제 PIVOT 쿼리 문자열 문을 사용합니다.

    SELECT * FROM 
    (SELECT myNumber, myLetter, myValue FROM myTable)
    PIVOT (Sum(myValue) AS val FOR myLetter IN (&str_in_statement));

    여기 출력은 다음과 같습니다

      MYNUMBER      A_VAL      B_VAL      C_VAL
---------- ---------- ---------- ----------
         1          2          4            
         2          8         10          6 
         3         14                    12

    그러나 제한이 있습니다. 당신은 4000 바이트 문자열을 연결할 수 있습니다.

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

    3.나중에 독자들을 위해, 여기에 또 다른 솔루션입니다 https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

    나중에 독자들을 위해, 여기에 또 다른 솔루션입니다 https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

    같은 쿼리를 수

    select * from table( pivot(  'select deptno,  job, count(*) c from scott.emp group by deptno,job' ) )
  4. ==============================

    4.사용 동적 쿼리

    사용 동적 쿼리

    테스트 코드는 다음과 같습니다

    --  DDL for Table TMP_TEST
--------------------------------------------------------

  CREATE TABLE "TMP_TEST" 
   (    "NAME" VARCHAR2(20), 
    "APP" VARCHAR2(20)
   );
/
SET DEFINE OFF;
Insert into TMP_TEST (NAME,APP) values ('suhaib','2');
Insert into TMP_TEST (NAME,APP) values ('suhaib','1');
Insert into TMP_TEST (NAME,APP) values ('shahzad','3');
Insert into TMP_TEST (NAME,APP) values ('shahzad','2');
Insert into TMP_TEST (NAME,APP) values ('shahzad','5');
Insert into TMP_TEST (NAME,APP) values ('tariq','1');
Insert into TMP_TEST (NAME,APP) values ('tariq','2');
Insert into TMP_TEST (NAME,APP) values ('tariq','6');
Insert into TMP_TEST (NAME,APP) values ('tariq','4');
/
  CREATE TABLE "TMP_TESTAPP" 
   (    "APP" VARCHAR2(20)
   );

SET DEFINE OFF;
Insert into TMP_TESTAPP (APP) values ('1');
Insert into TMP_TESTAPP (APP) values ('2');
Insert into TMP_TESTAPP (APP) values ('3');
Insert into TMP_TESTAPP (APP) values ('4');
Insert into TMP_TESTAPP (APP) values ('5');
Insert into TMP_TESTAPP (APP) values ('6');
/
create or replace PROCEDURE temp_test(
  pcursor out sys_refcursor,
    PRESULT                   OUT VARCHAR2
    )
AS
V_VALUES VARCHAR2(4000);
V_QUERY VARCHAR2(4000);
BEGIN
 PRESULT := 'Nothing';

-- concating activities name using comma, replace "'" with "''" because we will use it in dynamic query so "'" can effect query.
  SELECT DISTINCT 
         LISTAGG('''' || REPLACE(APP,'''','''''') || '''',',')
         WITHIN GROUP (ORDER BY APP) AS temp_in_statement 
    INTO V_VALUES
    FROM (SELECT DISTINCT APP 
            FROM TMP_TESTAPP);

-- designing dynamic query  

  V_QUERY := 'select * 
                from (  select NAME,APP 
                          from TMP_TEST   )   
               pivot (count(*) for APP in 
                     (' ||V_VALUES|| '))  
           order  by NAME' ;

    OPEN PCURSOR
     FOR V_QUERY;


 PRESULT := 'Success';

Exception
WHEN OTHERS THEN
 PRESULT := SQLcode || ' - ' || SQLERRM;
END temp_test;
  5. ==============================

    5.나는 (안톤 PL / SQL 사용자 정의 함수 피벗 ()) 위의 방법을 사용하고는 일을! 나는 전문 오라클 개발자 아니다, 이러한 내가했던 간단한 단계는 다음과 같습니다

    나는 (안톤 PL / SQL 사용자 정의 함수 피벗 ()) 위의 방법을 사용하고는 일을! 나는 전문 오라클 개발자 아니다, 이러한 내가했던 간단한 단계는 다음과 같습니다

    1) 거기에 pivotFun.sql을 찾을 수있는 압축 패키지를 다운로드합니다. 2) 실행은 pivotFun.sql하면 새로운 기능을 만들 수 있습니다 3) 일반 SQL의 기능을 사용합니다.

    그냥 동적 열 이름에주의하십시오. 내 환경에서 그 열 이름은 30 개 문자로 제한되며 그 안에 작은 따옴표를 포함 할 수 없습니다 발견했다. 그래서, 내 쿼리 이제이 같은 것입니다 :

    SELECT 
  *
FROM   
  table( 
        pivot('
                SELECT DISTINCT
                    P.proj_id,
                    REPLACE(substr(T.UDF_TYPE_LABEL, 1, 30), '''''''','','') as Attribute,
                    CASE
                      WHEN V.udf_text is null     and V.udf_date is null and      V.udf_number is NOT null  THEN to_char(V.udf_number)
                      WHEN V.udf_text is null     and V.udf_date is NOT null and  V.udf_number is null      THEN to_char(V.udf_date)
                      WHEN V.udf_text is NOT null and V.udf_date is null and      V.udf_number is null      THEN V.udf_text
                      ELSE NULL END
                    AS VALUE
                FROM
                    project   P
                LEFT JOIN UDFVALUE V ON P.proj_id     = V.proj_id 
                LEFT JOIN UDFTYPE  T ON V.UDF_TYPE_ID = T.UDF_TYPE_ID
                WHERE 
                    P.delete_session_id  IS NULL AND
                    T.TABLE_NAME = ''PROJECT''
    ')
)

    잘 1m 기록까지 작동합니다.

  6. ==============================

    6.나는 정확히 대신 난 그냥 할 수있는 방법을 동적 피벗 설명 될 것이다, 질문 영업 이익은 요청했습니다에 대한 대답을 않을거야.

    나는 정확히 대신 난 그냥 할 수있는 방법을 동적 피벗 설명 될 것이다, 질문 영업 이익은 요청했습니다에 대한 대답을 않을거야.

    여기에서는 초기 변수로 열 값을 검색하고, 가변 내부의 동적 SQL을 전달하여, 동적 SQL을 사용한다.

    예

    우리는 다음과 같은 테이블이 좋습니다.

    우리는 수량에서 해당 열에서 열 이름과 열 YR의 값과 값을 표시해야하는 경우, 우리는 아래의 코드를 사용할 수 있습니다.

    declare
  sqlqry clob;
  cols clob;
begin
  select listagg('''' || YR || ''' as "' || YR || '"', ',') within group (order by YR)
  into   cols
  from   (select distinct YR from EMPLOYEE);


  sqlqry :=
  '      
  select * from
  (
      select *
      from EMPLOYEE
  )
  pivot
  (
    MIN(QTY) for YR in (' || cols  || ')
  )';

  execute immediate sqlqry;
end;
/

    결과

    필요한 경우, 당신은 또한 임시 테이블을 생성하고 결과를보고하는 임시 테이블에서 선택 쿼리를 할 수 있습니다. 그것의 간단한, 그냥 위의 코드에서 CREATE TABLE TABLENAME AS를 추가합니다.

    sqlqry :=
'    
  CREATE TABLE TABLENAME AS
  select * from

    7. from https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 쉼표에서 MySQL의 쿼리 찾는 값은 문자열을 분리  (0) 2020.03.08
[SQL] 어떻게 하나의 컬럼 식별자 필드에 서브 쿼리에서 "가입"여러 행에 SQL 서버 기능을 만드는 방법? [복제]  (0) 2020.03.08
[SQL] 왜 오라클은 NULL로 빈 문자열을 취급 9I는 무엇입니까?  (0) 2020.03.08
[SQL] 어느 / 빠른 최고? SELECT * 또는 SELECT 컬럼 1, colum2, 3 열, 등  (0) 2020.03.08
[SQL] 는 SQL Server의 기능에 값을 쉼표로 가득한 VARCHAR 구분 전달  (0) 2020.03.08
,

티스토리툴바