세로 데이터를 가로로 변환

) 세로를 가로의 데이터로 만들기

/* Formatted on 2010/10/07 14:11 (Formatter Plus v4.8.8) */
WITH tmp AS
(SELECT '1' AS col1, 'A' AS col2
FROM DUAL
UNION ALL
SELECT '2' AS col1, '가' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'B' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'C' AS col2
FROM DUAL)
SELECT col1, LTRIM (SYS_CONNECT_BY_PATH (col2, ','), ',') AS col1
FROM (SELECT col1, col2,
ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col1) rn,
COUNT (*) OVER (PARTITION BY col1) cnt
FROM tmp)
WHERE LEVEL = cnt
START WITH rn = 1
CONNECT BY PRIOR col1 = col1 AND PRIOR rn = rn - 1;


2) 세로를 가로의 컬럼 데이터로 만들기
/* Formatted on 2010/10/07 16:07 (Formatter Plus v4.8.8) */
WITH tmp AS
(SELECT '1' AS col1, 'a' AS col2
FROM DUAL
UNION ALL
SELECT '2' AS col1, '가' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'b' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'c' AS col2
FROM DUAL)
SELECT col1, MIN (DECODE (r, 1, col2)), MIN (DECODE (r, 2, col2)),
MIN (DECODE (r, 2, col2))
FROM (SELECT col1, col2,
ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col2) r
FROM tmp)
GROUP BY col1

오라클 10g는 pivot 기능을 지원하지 않습니다. 11g 부터 지원가능한데 임시방편으로 사용할 수 있는기능

출처 : 펌

3) 세로를 가로의 컬럼 데이터로 만들기

WITH tmp AS
(
SELECT INS_YYYY, INS_MM, CORP_GB, S_SCR FROM GRDFORM // 1차가공
)

// 출력부
SELECT CORP_GB
,MIN(DECODE(R0,1,S_SCR)) ,MIN(DECODE(R0,2,S_SCR))
,MIN(DECODE(R1,1,S_SCR)), MIN(DECODE(R1,1,S_SCR))
,MIN(DECODE(R2,1,S_SCR)) ,MIN(DECODE(R2,2,S_SCR))
,MIN(DECODE(R3,1,S_SCR)), MIN(DECODE(R3,2,S_SCR))
,MIN(DECODE(R4,1,S_SCR)) ,MIN(DECODE(R4,2,S_SCR))

FROM (

// 2차가공
SELECT INS_YYYY, INS_MM, CORP_GB,S_SCR
,ROW_NUMBER() OVER(PARTITION BY INS_YYYY,CORP_GB ORDER BY INS_YYYY,INS_MM,CORP_GB) R0
,0 R1
,0 R2
,0 R3
,0 R4
FROM tmp
WHERE INS_YYYY = '2008'
UNION ALL
SELECT INS_YYYY, INS_MM, CORP_GB,S_SCR
,0 R0
,ROW_NUMBER() OVER(PARTITION BY INS_YYYY,CORP_GB ORDER BY INS_YYYY,INS_MM,CORP_GB) R1
,0 R2
,0 R3
,0 R4
FROM tmp
WHERE INS_YYYY = '2009'
UNION ALL
SELECT INS_YYYY, INS_MM, CORP_GB,S_SCR
,0 R0
,0 R1
,ROW_NUMBER() OVER(PARTITION BY INS_YYYY,CORP_GB ORDER BY INS_YYYY,INS_MM,CORP_GB) R2
,0 R3
,0 R4
FROM tmp
WHERE INS_YYYY = '2010'
UNION ALL

SELECT INS_YYYY, INS_MM, CORP_GB,S_SCR
,0 R0
,0 R1
,0 R2
,ROW_NUMBER() OVER(PARTITION BY INS_YYYY,CORP_GB ORDER BY INS_YYYY,INS_MM,CORP_GB) R3
,0 R4
FROM tmp
WHERE INS_YYYY = '2011'
UNION ALL
SELECT INS_YYYY, INS_MM, CORP_GB,S_SCR
,0 R0
,0 R1
,0 R2
,0 R3
,ROW_NUMBER() OVER(PARTITION BY INS_YYYY,CORP_GB ORDER BY INS_YYYY,INS_MM,CORP_GB) R4
FROM tmp
WHERE INS_YYYY = '2012'
)
GROUP BY CORP_GB
ORDER BY CORP_GB

4) 세로를 가로의 컬럼 데이터로 만들기

MSSQL문의 저장프로시저를 분석해서 자바 배치를 만드는 작업을 하던 중에.. 막히는 부분이 있었다.
MSSQL의 저장프로시저에는 무조건 쿼리를 만들어 임시테이블에 넣고,
그 임시테이블을 가지고 이것저것 조건을 건 다음에 다시 다른 임시테이블에 넣는 작업이 반복되고 있었다.

며칠을 고민한 끝에 오라클의 WITH문을 발견하고 '만세~!'를 불렀다. ㅋ
WITH문을 사용하면 자유자재로 쿼리를 뷰처럼 재사용할 수 있었기 때문이다.

WITH viewData AS
(
SELECT *
FROM TestTable_1
)
SELECT *
FROM viewData
;

이건 WITH 문의 기본 문장이다.
WITH name AS ( 쿼리 )

그런데, WITH문에 다중 쿼리를 사용하면 그 위력이 엄청나게 된다.

WITH viewData1 AS
(
SELECT *
FROM TestTable_1
),
WITH viewData2 AS
(
SELECT *
FROM TestTable_2
)
SELECT V1.id,
V2.name,
V2.address
FROM viewData1 V1,
viewData2 V2
WHERE V1.id = V2.id
AND V1.name LIKE '최%'
;

이런 정도의 쿼리라면 굳이 WITH문을 사용할 필요가 있을까?
다음 쿼리를 보자.

WITH viewData1 AS
(
SELECT *
FROM TestTable_1
),
WITH viewData2 AS
(
SELECT *
FROM TestTable_2
),
WITH viewData3 AS
(
SELECT V1.id,
V2.name,
V2.address
FROM viewData1 V1,
viewData2 V2
WHERE V1.id = V2.id
AND V1.name LIKE '최%'
)
SELECT *
FROM viewData3
WHERE ROWNUM <= 100;

이런식으로.. 위에서 정의한 viewData1, viewData2를 아래 WITH문에서 호출해서 사용할 수 있다.
갯수는 계속 추가해서 사용할 수 있으니 WITH문으로 임시테이블이나 View처럼 사용할 수 있어 좋다.

5) 오라클 WITH 문 사용하기

서브 쿼리의 결과가 반복되어 사용된다면 서브 쿼리를 여러 번 실행해야만 할까?

동일한 서브 쿼리의 동일한 결과를 사용하기 위해

동일한 서브 쿼리를 여러 번 실행시킨다면 너무 비효율적이다.

오라클에서는 이를 해결하기 위해 WITH clause를 제공한다.

WITH clause는 쿼리 결과에 네이밍을 함으로써 재사용이 가능하게 한다.

사용 형식은 다음과 같다.

다중 WITH문 사용시

WITH 절에사용된 테이블은 반드시 선언되어져야한다 안그러면 에러

WITH subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

사용 예제는 다음과 같다.

WITH
sum_sales AS
( select /*+ materialize */
sum(quantity) all_sales from stores ),
number_stores AS
( select /*+ materialize */
count(*) nbr_stores from stores ),
sales_by_store AS
( select /*+ materialize */
store_name, sum(quantity) store_sales from
store natural join sales )
SELECT
store_name
FROM
store,
sum_sales,
number_stores,
sales_by_store
where
store_sales > (all_sales / nbr_stores)

6) 오라클 WITH 문 사용하기- 간단한 사용예제

-- 다중 with문 사용시 with 절에 사용된 테이블은 반드시 선언되어져야한다.
-- with 문안에서 위 선언던 서브쿼리에대해서 명칭으로 가져다 쓸수있다.

WITH name AS (

),name2 AS(
-- with 문안에 위선언된 서브쿼리 대해 사용
select * from name..
),name3 AS(

)

SELECT
* FROM name,name2,name3