오라클 행 <-> 열 변환

테이블 생성 & 자료입력

--테이블생성
CREATE TABLE TEST001 (
COR_NAME VARCHAR2(3),
COR_SOFEWARE VARCHAR2(20)
);
COMMIT;

--자료삽입
INSERT INTO TEST001 VALUES ('001','aaaa');
INSERT INTO TEST001 VALUES ('002','bbbb');
INSERT INTO TEST001 VALUES ('003','cccc');
INSERT INTO TEST001 VALUES ('004','dddd');
INSERT INTO TEST001 VALUES ('005','eeee');
INSERT INTO TEST001 VALUES ('006','ffff');
INSERT INTO TEST001 VALUES ('007','gggg');
INSERT INTO TEST001 VALUES ('008','hhhh');
COMMIT;

SELECT * FROM TEST001

행 <-> 열 변환 츨력

(SELECT MAX(SYS_CONNECT_BY_PATH (COR_NAME , ' '))
FROM (
SELECT COR_NAME, ROWNUM RNUM
FROM TEST001
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM + 1 = RNUM)
UNION ALL
(SELECT MAX(SYS_CONNECT_BY_PATH (COR_SOFEWARE, ' '))
FROM (
SELECT COR_SOFEWARE, ROWNUM RNUM
FROM TEST001
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM + 1 = RNUM)

 

 

 

 

 

(SELECT MAX(SYS_CONNECT_BY_PATH (code , ' '))
FROM (
SELECT code, ROWNUM RNUM
FROM tab
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM + 1 = RNUM)
UNION ALL
(SELECT MAX(SYS_CONNECT_BY_PATH (data, ' '))
FROM (
SELECT data, ROWNUM RNUM
FROM tab
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM + 1 = RNUM)

삽지리
2008-05-14 16:10:01

초허접하지만 저도 도전요 ㅋ

--테이블생성
CREATE TABLE TB_TEST001 (
COL_NUMBER VARCHAR2(1),
COL_NAME VARCHAR2(3)
);
COMMIT;
--자료삽입
INSERT INTO TB_TEST001 VALUES ('1','aaa');
INSERT INTO TB_TEST001 VALUES ('2','bbb');
INSERT INTO TB_TEST001 VALUES ('3','ccc');
INSERT INTO TB_TEST001 VALUES ('4','ddd');
INSERT INTO TB_TEST001 VALUES ('5','eee');
INSERT INTO TB_TEST001 VALUES ('6','fff');
INSERT INTO TB_TEST001 VALUES ('7','ggg');
COMMIT;

--쿼리생성
SELECT
(SELECT COL_NUMBER FROM TB_TEST001 WHERE COL_NUMBER=1) 하나,
(SELECT COL_NUMBER FROM TB_TEST001 WHERE COL_NUMBER=2) 두울,
(SELECT COL_NUMBER FROM TB_TEST001 WHERE COL_NUMBER=3) 세엣,
(SELECT COL_NUMBER FROM TB_TEST001 WHERE COL_NUMBER=4) 네엣,
(SELECT COL_NUMBER FROM TB_TEST001 WHERE COL_NUMBER=5) 다섯,
(SELECT COL_NUMBER FROM TB_TEST001 WHERE COL_NUMBER=6) 여섯,
(SELECT COL_NUMBER FROM TB_TEST001 WHERE COL_NUMBER=7) 일곱
FROM DUAL
UNION
SELECT
(SELECT COL_NAME FROM TB_TEST001 WHERE COL_NUMBER=1) 하나,
(SELECT COL_NAME FROM TB_TEST001 WHERE COL_NUMBER=2) 두울,
(SELECT COL_NAME FROM TB_TEST001 WHERE COL_NUMBER=3) 세엣,
(SELECT COL_NAME FROM TB_TEST001 WHERE COL_NUMBER=4) 네엣,
(SELECT COL_NAME FROM TB_TEST001 WHERE COL_NUMBER=5) 다섯,
(SELECT COL_NAME FROM TB_TEST001 WHERE COL_NUMBER=6) 여섯,
(SELECT COL_NAME FROM TB_TEST001 WHERE COL_NUMBER=7) 일곱
FROM DUAL;

--테이블 삭제
DROP TABLE TB_TEST001;
COMMIT;
사탕수위
2008-05-14 16:34:43

--테이블생성
CREATE TABLE TB_TEST001 (
COL_NUMBER VARCHAR2(1),
COL_NAME VARCHAR2(3)
);
CREATE TABLE TB_TEST002 (
COL_NUMBER VARCHAR2(1)
);
COMMIT;
--자료삽입
INSERT INTO TB_TEST001 VALUES ('1','aaa');
INSERT INTO TB_TEST001 VALUES ('2','bbb');
INSERT INTO TB_TEST001 VALUES ('3','ccc');
INSERT INTO TB_TEST001 VALUES ('4','ddd');
INSERT INTO TB_TEST001 VALUES ('5','eee');
INSERT INTO TB_TEST001 VALUES ('6','fff');
INSERT INTO TB_TEST001 VALUES ('7','ggg');
INSERT INTO TB_TEST002 VALUES ('1');
INSERT INTO TB_TEST002 VALUES ('2');
COMMIT;

SELECT DECODE(T02.COL_NUMBER,2,SUBSTR(ROW_1,1,INSTR(ROW_1,'%')-1),
SUBSTR(ROW_1,INSTR(ROW_1,'%')+1,LENGTH(ROW_1)))ROW_1,
DECODE(T02.COL_NUMBER,2,SUBSTR(ROW_2,1,INSTR(ROW_2,'%')-1),
SUBSTR(ROW_2,INSTR(ROW_2,'%')+1,LENGTH(ROW_2)))ROW_2,
DECODE(T02.COL_NUMBER,2,SUBSTR(ROW_3,1,INSTR(ROW_3,'%')-1),
SUBSTR(ROW_3,INSTR(ROW_3,'%')+1,LENGTH(ROW_3)))ROW_3,
DECODE(T02.COL_NUMBER,2,SUBSTR(ROW_4,1,INSTR(ROW_4,'%')-1),
SUBSTR(ROW_4,INSTR(ROW_4,'%')+1,LENGTH(ROW_4)))ROW_4,
DECODE(T02.COL_NUMBER,2,SUBSTR(ROW_5,1,INSTR(ROW_5,'%')-1),
SUBSTR(ROW_5,INSTR(ROW_5,'%')+1,LENGTH(ROW_5)))ROW_5,
DECODE(T02.COL_NUMBER,2,SUBSTR(ROW_6,1,INSTR(ROW_6,'%')-1),
SUBSTR(ROW_6,INSTR(ROW_6,'%')+1,LENGTH(ROW_6)))ROW_6,
DECODE(T02.COL_NUMBER,2,SUBSTR(ROW_7,1,INSTR(ROW_7,'%')-1),
SUBSTR(ROW_7,INSTR(ROW_7,'%')+1,LENGTH(ROW_7)))ROW_7
FROM(
SELECT MAX(DECODE(T1.COL_NUMBER,1,T1.COL_NAME||'%'||T1.COL_NUMBER,NULL))ROW_1,
MAX(DECODE(T1.COL_NUMBER,2,T1.COL_NAME||'%'||T1.COL_NUMBER,NULL))ROW_2,
MAX(DECODE(T1.COL_NUMBER,3,T1.COL_NAME||'%'||T1.COL_NUMBER,NULL))ROW_3,
MAX(DECODE(T1.COL_NUMBER,4,T1.COL_NAME||'%'||T1.COL_NUMBER,NULL))ROW_4,
MAX(DECODE(T1.COL_NUMBER,5,T1.COL_NAME||'%'||T1.COL_NUMBER,NULL))ROW_5,
MAX(DECODE(T1.COL_NUMBER,6,T1.COL_NAME||'%'||T1.COL_NUMBER,NULL))ROW_6,
MAX(DECODE(T1.COL_NUMBER,7,T1.COL_NAME||'%'||T1.COL_NUMBER,NULL))ROW_7
FROM TB_TEST001 T1
GROUP BY 1)T01,
TB_TEST002 T02
후니
2008-05-14 17:09:02

그냥 섭쿼리쓴거유니온하면되지만
유니온은 위에서 답해서 다른 방법으로
후니
2008-05-14 17:10:12

 

생각해보니 어짜피 서브쿼리로 내린거 뭐하러 컬럼 이어붙였지?
후니
2008-05-14 23:40:49

테이블을 한번만 읽어도 무리가 없는경우는 union all사용
select max(decode(col_number,1,col_number)) col1
,max(decode(col_number,2,col_number))col2
,max(decode(col_number,3,col_number))col3
,max(decode(col_number,4,col_number))col4
,max(decode(col_number,5,col_number))col5
,max(decode(col_number,6,col_number))col6
,max(decode(col_number,7,col_number))col7
from tb_test001
union all
select
max(decode(col_number,1,col_name))col1
,max(decode(col_number,2,col_name))col2
,max(decode(col_number,3,col_name))col3
,max(decode(col_number,4,col_name))col4
,max(decode(col_number,5,col_name))col5
,max(decode(col_number,6,col_name))col6
,max(decode(col_number,7,col_name))col7
from TB_TEST001
아닌경우는 데이타 복제
select
max(decode(no,1,decode(col_number,1,col_number),2,decode(col_number,1,col_name))) col1
 ,max(decode(no,1,decode(col_number,2,col_number),2,decode(col_number,2,col_name))) col2
 ,max(decode(no,1,decode(col_number,3,col_number),2,decode(col_number,3,col_name))) col3
 ,max(decode(no,1,decode(col_number,4,col_number),2,decode(col_number,4,col_name))) col4
 ,max(decode(no,1,decode(col_number,5,col_number),2,decode(col_number,5,col_name))) col5
 ,max(decode(no,1,decode(col_number,6,col_number),2,decode(col_number,6,col_name))) col6
 ,max(decode(no,1,decode(col_number,7,col_number),2,decode(col_number,7,col_name))) col7
 from tb_test001 a
,(
select level no from dual connect by level <=2
)b
group by decode(no,1,1,2)

재밌네요 ㅋㅋ
혈기린
2008-05-15 13:45:53

열의 갯수가 고정되어 있다면 다음과 같이 쉽게 해결할 수 있습니다.
select max(decode(code,'1',data)),max(decode(code,'2',data)),max(decode(code,'3',data)),max(decode(code,'4',data)),max(decode(code,'5',data)),max(decode(code,'6',data)),max(decode(code,'7',data))
 from 테이블명
현
2008-05-15 20:42:50

추가로 설명드리면 ORACLE 디비를 사용하는 이유를 조금이나마 느끼게 해주고싶었어요..
그리고 사탕수위님 ㅎㅎㅎ 마지막에 지우는 이유는? ㅋㅋㅋㅋ
그리고 삽지리님은 결과값한번 올려주세요......
헬기린님 그룹바이에 decode 쓰면 부하가 좀 크지 않을까....
모두들 수고하셨구요!!~
다음을 기대합니다!!
참◈서빈
2008-05-19 22:30:55

참 서빈님 그굽바이 decode를 쓰면부하가 크다는 말씀이 decode의 부하를 말씀하시는건지 아님 그룹바이의 부하를 말씀하시는건가요? 전 실무에서도 이런방법을 많이 사용있는데 select decode(no,1,1,2), ~~~ from tab where ~~ group by decode(no,1,1,2) 일때 decode(no,1,1,2) 이 decode문은 group by 절에서 한번만 사용되는걸로 알고 있습니다 select절에서는 실행되지 않죠 그럼 decode의 부하는 그렇게 크지 않을거 같은데요 그룹바이 의 부하도 마찬가지로 code와 data로 집합을 구분하는거 뿐이 안되기때문에 부하도 미미할거 같아서요
 그리고 후니님의 쿼리를 보시면 select절에 decode문안의 sustrt instr length등의 함수의 부하가 더큰걸로 알고 있습니다 데이타 건수가 많아 지면 많아질수록 더큰부하가 걸리겠죠 현님 쿼리 결과는 code와 data중 data만 나오는 쿼리가 아닌가요?
 이상 제가 알고 있는 사항이었습니다 제가 잘못알고 있었나요 ㅜ.ㅡ 전 이렇게 배웠는데 ^^ 알려주세요 ~~
혈기린
2008-05-20 09:15:51

제가 좋은 점수 받은껀 아닌거 같은데 부끄부끄-0-;;
섭쿼리로 내렸으면서 컬럼 이어붙이고 다시 나눈거나
group by 1한거나-_-;;;
그냥 결과값만 보려고 하다보니-0-;;;
후니
2008-05-20 10:04:55

--최종정리한거(select level no from dual connect by level <=2 적용 ㅋㅋ
SELECT
decode(t02.no,2,t01.row_1_name,row_1_num)col_1,
decode(t02.no,2,t01.row_2_name,row_2_num)col_2,
decode(t02.no,2,t01.row_3_name,row_3_num)col_3,
decode(t02.no,2,t01.row_4_name,row_4_num)col_4,
decode(t02.no,2,t01.row_5_name,row_5_num)col_5,
decode(t02.no,2,t01.row_6_name,row_6_num)col_6,
decode(t02.no,2,t01.row_7_name,row_7_num)col_7
FROM(
SELECT
MAX(DECODE(T1.COL_NUMBER,1,T1.COL_NAME,NULL))ROW_1_name,
MAX(DECODE(T1.COL_NUMBER,2,T1.COL_NAME,NULL))ROW_2_name,
MAX(DECODE(T1.COL_NUMBER,3,T1.COL_NAME,NULL))ROW_3_name,
MAX(DECODE(T1.COL_NUMBER,4,T1.COL_NAME,NULL))ROW_4_name,
MAX(DECODE(T1.COL_NUMBER,5,T1.COL_NAME,NULL))ROW_5_name,
MAX(DECODE(T1.COL_NUMBER,6,T1.COL_NAME,NULL))ROW_6_name,
MAX(DECODE(T1.COL_NUMBER,7,T1.COL_NAME,NULL))ROW_7_name,
MAX(DECODE(T1.COL_NUMBER,1,T1.COL_NUMBER,NULL))ROW_1_num,
MAX(DECODE(T1.COL_NUMBER,2,T1.COL_NUMBER,NULL))ROW_2_num,
MAX(DECODE(T1.COL_NUMBER,3,T1.COL_NUMBER,NULL))ROW_3_num,
MAX(DECODE(T1.COL_NUMBER,4,T1.COL_NUMBER,NULL))ROW_4_num,
MAX(DECODE(T1.COL_NUMBER,5,T1.COL_NUMBER,NULL))ROW_5_num,
MAX(DECODE(T1.COL_NUMBER,6,T1.COL_NUMBER,NULL))ROW_6_num,
MAX(DECODE(T1.COL_NUMBER,7,T1.COL_NUMBER,NULL))ROW_7_num
FROM TB_TEST001 T1
)T01,
(select level no from dual connect by level <=2)
T02
후니
2008-05-20 10:25:28

SELECT
max(decode(t2.no,2,DECODE(T1.COL_NUMBER,1,T1.COL_NAME),DECODE(T1.COL_NUMBER,1,T1.COL_NUMBER)) ) col_1,
 max(decode(t2.no,2,DECODE(T1.COL_NUMBER,2,T1.COL_NAME),DECODE(T1.COL_NUMBER,2,T1.COL_NUMBER)) ) col_2,
 max(decode(t2.no,2,DECODE(T1.COL_NUMBER,3,T1.COL_NAME),DECODE(T1.COL_NUMBER,3,T1.COL_NUMBER)) ) col_3,
 max(decode(t2.no,2,DECODE(T1.COL_NUMBER,4,T1.COL_NAME),DECODE(T1.COL_NUMBER,4,T1.COL_NUMBER)) ) col_4,
 max(decode(t2.no,2,DECODE(T1.COL_NUMBER,5,T1.COL_NAME),DECODE(T1.COL_NUMBER,5,T1.COL_NUMBER)) ) col_5,
 max(decode(t2.no,2,DECODE(T1.COL_NUMBER,6,T1.COL_NAME),DECODE(T1.COL_NUMBER,6,T1.COL_NUMBER)) ) col_6,
 max(decode(t2.no,2,DECODE(T1.COL_NUMBER,7,T1.COL_NAME),DECODE(T1.COL_NUMBER,7,T1.COL_NUMBER)) ) col_7
 FROM TB_TEST001 T1 ,
(select level no from dual connect by level <=2)
T2
group by t2.no
후니
2008-05-22 11:26:52

코스트는 이거더 높네요
후니
2008-05-22 11:27:32

보기는 안좋아도 GROUP BY 안하는게 역시
후니
2008-05-22 11:31:49

마지막에 지우는 이유는;;;;;;
테이블이 너무 많아서 관리가 안돼요 ㅜㅠ
이런 test 테이블은 쓰고 결과만 보고 바로 지운다능 ㅋ
사탕수위
2008-05-22 15:27:05

혈기린 님...
SQL 수준이 상당하시네요...
제가 물은건 혈기린님께서 다른분의 SQL 에 대해 조언을 할수 있지 않을까해서
한 말인데.. 전달이 매끄럽지 못했나보네요
참◈서빈
2008-05-23 09:34:28

참서빈 님 아닙니다 전 제가 잘못알고 있는줄 알고 질문한거였습니다 -.-;;
상당하긴요 정말 잘하는사람 보면 바로 버로우 타는 실력인지라 sql퀴즈는 예전 엔코아 홈페이지에 있던 퀴즈 문제가 sql 공부하기는 최고였는데 지금은 서비스를 내려버려서 책으로 출판하신다구 했는데 아직 감감이네요 ^^
 혈기린
2008-05-23 13:11:30

테이블 생성
------------
CREATE TABLE TEST_SABJILI
(
CODE NUMBER,
DATA VARCHAR2(10 BYTE)
)
--------
insert
--------
SET DEFINE OFF;
Insert into TEST_SABJILI
(CODE, DATA)
Values
(1, 'aaa');
Insert into TEST_SABJILI
(CODE, DATA)
Values
(2, 'bbb');
Insert into TEST_SABJILI
(CODE, DATA)
Values
(3, 'ccc');
Insert into TEST_SABJILI
(CODE, DATA)
Values
(4, 'ddd');
Insert into TEST_SABJILI
(CODE, DATA)
Values
(5, 'eee');
Insert into TEST_SABJILI
(CODE, DATA)
Values
(6, 'fff');
Insert into TEST_SABJILI
(CODE, DATA)
Values
(7, 'ggg');
COMMIT;
-------------
조회
-------------
(SELECT MAX(SYS_CONNECT_BY_PATH (code , ' '))
FROM (
SELECT code, ROWNUM RNUM
FROM test_sabjili
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM + 1 = RNUM)
UNION ALL
(SELECT MAX(SYS_CONNECT_BY_PATH (data, ' '))
FROM (
SELECT data, ROWNUM RNUM
FROM test_sabjili
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM + 1 = RNUM)
---------------------
결과
---------------------
1 2 3 4 5 6 7
aaa bbb ccc ddd eee fff ggg
삽지리
2008-05-23 13:29:06