Oracle SQL의 동적 피벗
피벗(B에 대한 합(A))
이제 B는 데이터 유형 varchar2이고 X는 쉼표로 구분된 varchar2 값의 문자열입니다.
X에 대한 값은 동일한 표의 열(예: CL)에서 선택된 고유한 값입니다.피벗 쿼리가 이 방식으로 작동했습니다.
하지만 문제는 CL 열에 새 값이 있을 때마다 X 문자열에 수동으로 추가해야 한다는 것입니다.
저는 CL에서 선택된 고유한 값으로 X를 교체하려고 했습니다.그러나 쿼리가 실행되고 있지 않습니다.
제가 느낀 이유는 X를 대체하기 위해서는 쉼표로 구분된 값이 필요하기 때문입니다.
그런 다음 X 문자열과 일치하도록 정확한 출력을 반환하는 함수를 만들었습니다.그러나 쿼리가 여전히 실행되지 않습니다.
표시된 오류 메시지는 "오른쪽 단락 누락", "파일 통신 채널 종료" 등입니다.
그냥 피벗 대신 피벗 xml을 시도했는데, 쿼리가 실행되지만 oraxxx 등 값이 전혀 없는 값을 제공합니다.
제가 그것을 제대로 사용하지 않는 것 같습니다.
동적 값으로 피벗을 만드는 방법을 알려주시겠습니까?
원하는 출력보다 낮은 출력을 출력하는 피벗 XML을 사용하지 않고는 피벗의 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"에 넣습니다.우리는 COLUMN 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
이제 피벗 쿼리에서 String 문을 사용합니다.
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바이트의 문자열만 연결할 수 있습니다.
상수가 아닌 문자열은 다음에 넣을 수 없습니다.IN피벗 절의 절입니다.
여기에는 피벗 XML을 사용할 수 있습니다.
설명서에서:
subquery 하위 쿼리는 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;
나중에 사용할 수 있는 다른 솔루션이 있습니다. 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' ) )
저는 OP가 질문한 질문에 대한 정확한 답변을 하지 않을 것이며, 대신 동적 피벗이 수행될 수 있는 방법을 설명하겠습니다.
여기서는 처음에 열 값을 변수로 검색하고 변수를 동적 SQL 내부로 전달하여 동적 SQL을 사용해야 합니다.
예
아래와 같은 테이블이 있다고 생각합니다.
,YR열 이름 및 해당 열의 값으로 지정합니다.QTY그러면 우리는 아래 코드를 사용할 수 있습니다.
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
동적 쿼리 사용
테스트 코드는 다음과 같습니다.
-- 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;
위의 방법(Anton PL/SQL 사용자 정의 함수 피벗())을 사용하여 작업을 완료했습니다!저는 전문적인 Oracle 개발자가 아니기 때문에 다음과 같은 간단한 단계를 수행했습니다.
zip 패키지를 다운로드하여 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 레코드에서 잘 작동합니다.
(그리고 아직 사용하지 않은 Polymorphic Table Functions)의 도입으로 Oracle 19c 이후 추가 개발 노력 없이 가능해진 것 같습니다.
create table t as select trunc(level/5) as id , chr(65+mod(level, 5)) as code , level as val from dual connect by level < 10
create function f_pivot return varchar2 SQL_MACRO(TABLE) is l_codes varchar2(1000); begin select listagg( distinct '''' || code || ''' as ' || code, ',') into l_codes from t; return 'select * from t pivot ( max(val) for code in ( ' || l_codes || '))'; end; /
select * from f_pivot()ID | B | C | D | E | A-: | -: | -: | -: | -: | ---:0 | 1 | 2 | 3 | 4 | null1 | 6 | 7 | 8 | 9 | 5
이슈 ((일우의)(▁of▁case경문우▁()의 경우)SQL_MACRO접근법)은 결과 집합이 한 세션 동안 구조를 변경하지 않는 것입니다.
insert into t values(1, 'Q', 100); commit; select * from f_pivot()ID | B | C | D | E | A-: | -: | -: | -: | -: | ---:0 | 1 | 2 | 3 | 4 | null1 | 6 | 7 | 8 | 9 | 5
그러나 별도의 세션에서는 정상적으로 작동합니다.
select dbms_xmlgen.getxml('select * from f_pivot()') as v from dual
| V |
|---|
version=xml version="1.0"?> |
용사를 합니다.with function기능 동적 피벗은 사전 정의된 기능 없이 제자리에서 사용할 수 있습니다.
with function f_pivot1 return varchar2 SQL_MACRO(TABLE) is l_codes varchar2(1000); begin select listagg(distinct '''' || code || ''' as ' || code, ',') into l_codes from t; return 'select * from t pivot ( max(val) for code in ( ' || l_codes || '))'; end; select * from f_pivot1()ID | B | C | D | E | A | Q-: | -: | -: | -: | -: | ---: | ---:0 | 1 | 2 | 3 | 4 | null | null1 | 6 | 7 | 8 | 9 | 5 | 100
db<>여기로 이동
피벗 XML을 사용하지 않고는 피벗의 IN 문에 동적 문을 넣을 수 없지만 작은 기술을 사용하여 피벗에서 동적 문을 사용할 수 있습니다.PL/SQL에서 문자열 값 내에서 두 아포스트로피는 하나의 아포스트로피와 같습니다.
declare
sqlqry clob;
search_ids varchar(256) := '''2016'',''2017'',''2018'',''2019''';
begin
search_ids := concat( search_ids,'''2020''' ); -- you can append new search id dynamically as you wanted
sqlqry :=
'
select * from
(
select *
from EMPLOYEE
)
pivot
(
MIN(QTY) for YR in (' || search_ids || ')
)';
execute immediate sqlqry;
end;
결과를 한 Oracle XML 형식 결과를 반환하지 않는 한 XML 형식은 없습니다. "XML" "PL/SQL"의 PL/할 수 .SYS_REFCURSOR 타입 품반타입입
조건부 집계 포함
CREATE OR REPLACE FUNCTION Get_Jobs_ByYear RETURN SYS_REFCURSOR IS v_recordset SYS_REFCURSOR; v_sql VARCHAR2(32767); v_cols VARCHAR2(32767); BEGIN SELECT LISTAGG( 'SUM( CASE WHEN job_title = '''||job_title||''' THEN 1 ELSE 0 END ) AS "'||job_title||'"' , ',' ) WITHIN GROUP ( ORDER BY job_title ) INTO v_cols FROM ( SELECT DISTINCT job_title FROM jobs j ); v_sql := 'SELECT "HIRE YEAR",'|| v_cols || ' FROM ( SELECT TO_NUMBER(TO_CHAR(hire_date,''YYYY'')) AS "HIRE YEAR", job_title FROM employees e JOIN jobs j ON j.job_id = e.job_id ) GROUP BY "HIRE YEAR" ORDER BY "HIRE YEAR"'; OPEN v_recordset FOR v_sql; DBMS_OUTPUT.PUT_LINE(v_sql); RETURN v_recordset; END; /피벗 절 포함
CREATE OR REPLACE FUNCTION Get_Jobs_ByYear RETURN SYS_REFCURSOR IS v_recordset SYS_REFCURSOR; v_sql VARCHAR2(32767); v_cols VARCHAR2(32767); BEGIN SELECT LISTAGG( ''''||job_title||''' AS "'||job_title||'"' , ',' ) WITHIN GROUP ( ORDER BY job_title ) INTO v_cols FROM ( SELECT DISTINCT job_title FROM jobs j ); v_sql := 'SELECT * FROM ( SELECT TO_NUMBER(TO_CHAR(hire_date,''YYYY'')) AS "HIRE YEAR", job_title FROM employees e JOIN jobs j ON j.job_id = e.job_id ) PIVOT ( COUNT(*) FOR job_title IN ( '|| v_cols ||' ) ) ORDER BY "HIRE YEAR"'; OPEN v_recordset FOR v_sql; DBMS_OUTPUT.PUT_LINE(v_sql); RETURN v_recordset; END; /
하지만 단점이 있습니다.LISTAGG() 그것은 ORA-01489 코드입니다. 첫 번째 인수 내의 연결된 문자열이 4000자를 초과할 때마다 문자열 연결 결과가 너무 깁니다.이 경우, 다음 값을 반환하는 쿼리v_cols는 다가음대수있체니습다될로로 될 수 .XMLELEMENT() 내에 XMLAGG()를 들면
CREATE OR REPLACE FUNCTION Get_Jobs_ByYear RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_cols VARCHAR2(32767);
BEGIN
SELECT RTRIM(DBMS_XMLGEN.CONVERT(
XMLAGG(
XMLELEMENT(e, 'SUM( CASE WHEN job_title = '''||job_title||
''' THEN 1 ELSE 0 END ) AS "'||job_title||'",')
).EXTRACT('//text()').GETCLOBVAL() ,1),',') AS "v_cols"
FROM ( SELECT DISTINCT job_title
FROM jobs j);
v_sql :=
'SELECT "HIRE YEAR",'|| v_cols ||
' FROM
(
SELECT TO_NUMBER(TO_CHAR(hire_date,''YYYY'')) AS "HIRE YEAR", job_title
FROM employees e
JOIN jobs j
ON j.job_id = e.job_id
)
GROUP BY "HIRE YEAR"
ORDER BY "HIRE YEAR"';
DBMS_OUTPUT.put_line(LENGTH(v_sql));
OPEN v_recordset FOR v_sql;
RETURN v_recordset;
END;
/
VARCHAR2 유형에 대한 상한 32767을 초과하지 않는 한.이 마지막 방법은 Oracle 11g Release 2 이전 버전의 데이터베이스에도 적용될 수 있습니다. 이는 다음과 같은 기능이 포함되어 있지 않기 때문입니다.LISTAGG()기능.
그나저나, 아직도LISTAGG() 시 사용할 수 .v_cols데이터베이스 버전이 12.2+인 경우 ORA-01489 오류를 가져오지 않고 생성된 매우 긴 연결 문자열에 대해서도 ORA-01489 오류가 발생하는 동안 OVERFLOW TRUNCATE 절을 사용하여 문자열의 후행 부분이 잘립니다.
LISTAGG( <concatenated string>,',' ON OVERFLOW TRUNCATE 'THE REST IS TRUNCATED' WITHOUT COUNT )
이 함수는 다음과 같이 호출할 수 있습니다.
VAR rc REFCURSOR EXEC :rc := Get_Jobs_ByYear; PRINT rcSQL Developer's 명령줄에서
또는
BEGIN :result := Get_Jobs_ByYear; END;PL/SQL Developer의 Test 창에서 결과 집합을 가져옵니다.
오픈 소스 프로그램 Method4를 사용하여 단일 SQL 문에 있는 데이터를 동적으로 피벗할 수 있습니다.피벗.
패키지를 설치한 후 함수를 호출하고 SQL 문을 문자열로 전달합니다.SQL 문의 마지막 열은 값을 정의하고 두 번째 열은 열 이름을 정의합니다.기본 집계 함수는 MAX이며 다음과 같은 일반적인 엔티티 속성 값 쿼리에 잘 작동합니다.
select * from table(method4.pivot(
q'[
select 'A' name, 1 value from dual union all
select 'B' name, 2 value from dual union all
select 'C' name, 3 value from dual
]'
));
A B C
- - -
1 2 3
또한 이 프로그램은 매개 변수 P_AGGRATE_FUNCTION을 통해 다양한 집계 함수를 지원하며, PIBOT_COLUM_ID라는 열을 추가할 경우 사용자 지정 열 이름 순서를 지정할 수 있습니다.
패키지는 안톤의 피벗과 유사한 Oracle Data Cartridge 접근 방식을 사용하지만 Method4.피벗에는 다음과 같은 몇 가지 중요한 이점이 있습니다.
- 블로그의 Zip 파일뿐만 아니라 레포, 설치 지침, 라이센스, 장치 테스트, 설명서 및 설명이 포함된 일반적인 오픈 소스 프로그램.
- 비정상적인 열 이름을 처리합니다.
- 플로트와 같은 비정상적인 데이터 유형을 처리합니다.
- 최대 1000개의 열을 처리합니다.
- 일반적인 실수에 대해 의미 있는 오류 메시지를 제공합니다.
- NULL 열 이름을 처리합니다.
- 128자의 열 이름을 처리합니다.
- 잘못된 암시적 변환을 방지합니다.
- 문장을 매번 하드 파싱하여 기본 테이블 변경사항을 포착합니다.
그러나 대부분의 사용자는 여전히 애플리케이션 계층이나 피벗 XML 옵션에서 동적 피벗을 만드는 것이 좋습니다.
언급URL : https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracles-sql
'programing' 카테고리의 다른 글
| 다중 인스턴스를 사용한 데이터베이스 액세스 (0) | 2023.07.12 |
|---|---|
| SQL Server에서 날짜를 검색하는 중; Current_TIMestamp vs GetDate() (0) | 2023.07.12 |
| 브라우저 언어 탐지 (0) | 2023.07.12 |
| 스프링 통합 테스트: 기본 리소스 위치를 검색할 수 없습니다. (0) | 2023.07.07 |
| 엔티티 관리자를 사용하는 경우 해당 이름에 대해 정의된 쿼리가 없습니다. (0) | 2023.07.07 |