Oracle 서버에 연결된 서버가있는 SQL Server 인스턴스가 있습니다. Oracle 서버 PersonOptions
에는 다음과 같은 데이터가 포함 된 테이블 이 있습니다.
╔══════════╦══════════╗
║ PersonID ║ OptionID ║
╠══════════╬══════════╣
║ 1 ║ A ║
║ 1 ║ B ║
║ 2 ║ C ║
║ 3 ║ B ║
║ 4 ║ A ║
║ 4 ║ C ║
╚══════════╩══════════╝
그 데이터를 피벗해야 결과가 다음과 같습니다.
╔══════════╦═════════╦══════════╦══════════╗
║ PersonID ║ OptionA ║ Option B ║ Option C ║
╠══════════╬═════════╬══════════╬══════════╣
║ 1 ║ 1 ║ 1 ║ ║
║ 2 ║ ║ ║ 1 ║
║ 3 ║ ║ 1 ║ ║
║ 4 ║ 1 ║ ║ 1 ║
╚══════════╩═════════╩══════════╩══════════╝
어떤 제안?
답변
이 데이터 변환을 수행 할 수있는 몇 가지 방법이 있습니다. PIVOT
함수에 액세스 하면 가장 쉽습니다. 그렇지 않은 경우 집계 함수 및을 사용할 수 있습니다 CASE
.
집계 / 케이스 버전 :
select personid,
max(case when optionid = 'A' then 1 else 0 end) OptionA,
max(case when optionid = 'B' then 1 else 0 end) OptionB,
max(case when optionid = 'C' then 1 else 0 end) OptionC
from PersonOptions
group by personid
order by personid;
정적 피벗 :
select *
from
(
select personid, optionid
from PersonOptions
) src
pivot
(
count(optionid)
for optionid in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv
order by personid
동적 버전 :
위의 두 버전은 알려진 수의 값이있는 경우 효과적이지만 값을 알 수없는 경우 동적 SQL을 구현하고 Oracle에서 프로 시저를 사용할 수 있습니다.
CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
sql_query varchar2(1000) := 'select personid ';
begin
for x in (select distinct OptionID from PersonOptions order by 1)
loop
sql_query := sql_query ||
' , min(case when OptionID = '''||x.OptionID||''' then 1 else null end) as Option_'||x.OptionID;
dbms_output.put_line(sql_query);
end loop;
sql_query := sql_query || ' from PersonOptions group by personid order by personid';
dbms_output.put_line(sql_query);
open p_cursor for sql_query;
end;
/
그런 다음 결과를 반환하면 다음을 사용합니다.
variable x refcursor
exec dynamic_pivot_po(:x)
print x
결과는 모든 버전에서 동일합니다.
| PERSONID | OPTIONA | OPTIONB | OPTIONC |
------------------------------------------
| 1 | 1 | 1 | 0 |
| 2 | 0 | 0 | 1 |
| 3 | 0 | 1 | 0 |
| 4 | 1 | 0 | 1 |
답변
이것은 SQL Server 구문과 동일합니다. Oracle 문서를 읽은 결과, NULLIF 및 PIVOT 은 SQL Server 유형과 동일한 형식으로 나타납니다. Itzik이 보여 주듯이 쿼리를 동적으로 만들지 않으면 정적이어야하는 피벗 목록이 문제 이지만 P / SQL로 변환 할 수 있는지 모르겠습니다.
WITH PersonOptions(PersonID, OptionId) AS
(
SELECT 1, 'A'
UNION ALL SELECT 1, 'B'
UNION ALL SELECT 2, 'C'
UNION ALL SELECT 3, 'B'
UNION ALL SELECT 4, 'A'
UNION ALL SELECT 4, 'C'
)
SELECT
P.PersonId
, NULLIF(P.A, 0) AS OptionA
, NULLIF(P.B, 0) AS OptionB
, NULLIF(P.C, 0) AS OptionC
FROM
PersonOptions PO
PIVOT
(
COUNT(PO.OptionId)
FOR OPtionId IN (A, B, C)
) P;
답변
쿼리를 수동으로 피벗하는 것을 선호하지만 사용할 수도 있습니다 PIVOT
.
SELECT PersonID,
MAX(CASE WHEN OptionId ='A' THEN 1 END) AS OptionA,
MAX(CASE WHEN OptionId ='B' THEN 1 END) AS OptionB,
MAX(CASE WHEN OptionId ='C' THEN 1 END) AS OptionC
FROM PersonOptions
GROUP BY PersonID