데이터베이스 질문과 답변

row의 출력 형식 지정 관련 질문입니다. 0 6 4,296

by 커피요쿠르트d [SQL Query] oracle query [2013.02.13 19:24:21]

수고하십니다.

쿼리 짜다가 질문이 있어 글 올립니다.

질문의 제목이 맞는지 모르겠네요.

데이터를 통해 설명드리겠습니다.

제가 얻은 데이터를 간단히 표현하면 다음과 같습니다.

WITH TEST_TBL AS(
SELECT '01' AS CODE, 'M' AS SEX , '0000' AS VALUE FROM DUAL UNION ALL
SELECT '01' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '02' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '03' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '03' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '04' AS CODE, 'M' AS SEX , '0003' AS VALUE FROM DUAL UNION ALL
SELECT '05' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '05' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '06' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL 
)SELECT * FROM TEST_TBL;



보시면 CODE 값이 있고 성별인 SEX 값이 있습니다. 

CODE별 사용자의 정보를 집계한 결과인데요.
CODE에 남자와 여자 사용자가 모두 있으면 CODE='01' 처럼 SEX='M' 로우와 SEX='F' 로우가 모두 나오는데
여자 사용자가 없는 CODE='02' 같은 경우는...SEX='M' 로우 밖에 나오지 않아서요.

저는 사용자가 있건 없건 모든 CODE가 SEX='M' 로우와 SEX='F' 로우를 갖는 결과를 갖고 싶습니다.


아래와 같이요..



WITH TEST_TBL AS(
SELECT '01' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '01' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '02' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '02' AS CODE, 'F' AS SEX , '0000' AS VALUE FROM DUAL UNION ALL
SELECT '03' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '03' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '04' AS CODE, 'M' AS SEX , '0003' AS VALUE FROM DUAL UNION ALL
SELECT '04' AS CODE, 'F' AS SEX , '0000' AS VALUE FROM DUAL UNION ALL
SELECT '05' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '05' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '06' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '06' AS CODE, 'F' AS SEX , '0000' AS VALUE FROM DUAL 
)SELECT * FROM TEST_TBL;



이렇게 나오는 방법이 있을 까요?

아 참고로 위의 결과는 CODE별고 사용자의 수를 집계한 쿼리의 결과입니다. GROUP BY ROLLUP을 사용하였습니다.

그리고 혹시 더 필요한 정보가 있으시면 말씀해주세요. 



그리고... 하나만 더..

이 질문의 제목을 뭐라고 하면 좋을까요?

하고자 하는 것을 한 줄로 표현하지 못하겠네요. ㅋ 검색도 안되고. ㅠㅠ

그럼 도움 바랍니다. 감사합니다.


참고로 사용하는 DB는 오라클 9 입니다.
by 아린 [2013.02.13 20:15:19]
오라클 9i 면 테이블을 두번 읽어야 할 듯 하네요.


WITH TEST_TBL AS(
SELECT '01' AS CODE, 'M' AS SEX , '0000' AS VALUE FROM DUAL UNION ALL
SELECT '01' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '02' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '03' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '03' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '04' AS CODE, 'M' AS SEX , '0003' AS VALUE FROM DUAL UNION ALL
SELECT '05' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '05' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '06' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL 
)
SELECT d.code, d.sex, NVL(t.value, '0000') value
  FROM (SELECT t.code, d.sex
          FROM (SELECT DISTINCT code FROM test_tbl) t
             , (SELECT DECODE(LEVEL, 1, 'M', 'F') sex FROM dual CONNECT BY LEVEL <= 2) d
        ) d, test_tbl t
 WHERE d.code = t.code(+)
   AND d.sex = t.sex(+)        
 ORDER BY d.code, d.sex DESC     
by 커피요쿠르트d [2013.02.13 23:51:53]
아린님 답변 감사합니다!!

내일 아침에 출근하는데로 적용해봐야겠습니다.

그런데.. 

혹시 오라클 10이면. 다른 방법이 있을까요?

혹시 이 댓글을 보신다면 .. 아신다면..
알려주시면 감사하겠습니다.

편안한 밤 되셔요. 
by 제로 [2013.02.14 09:52:37] 
WITH TEST_TBL AS(
SELECT '01' AS CODE, 'M' AS SEX , '0000' AS VALUE FROM DUAL UNION ALL
SELECT '01' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '02' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '03' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '03' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '04' AS CODE, 'M' AS SEX , '0003' AS VALUE FROM DUAL UNION ALL
SELECT '05' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '05' AS CODE, 'F' AS SEX , '0001' AS VALUE FROM DUAL UNION ALL
SELECT '06' AS CODE, 'M' AS SEX , '0001' AS VALUE FROM DUAL 
), T2 AS 
(SELECT 'M' SEX FROM DUAL
 UNION ALL 
 SELECT 'F' FROM DUAL
)
SELECT T1.CODE AS CODE
   , T2.SEX AS SEX
   , NVL(T1.VALUE, '0000') AS VALUE
FROM TEST_TBL T1 PARTITION BY (T1.CODE) RIGHT OUTER JOIN T2
ON T1.SEX = T2.SEX  
ORDER BY CODE, SEX DESC  
;
by 손님 [2013.02.14 10:03:11]
http://www.gurubee.net/article/54749
이 글 함 참고해 보면 될듯합니다.
by 마농 [2013.02.14 11:55:41] 
-- 1. Partition Outer Join --
SELECT b.code
     , a.sex
     , NVL(b.value, '0000') value
  FROM (SELECT 'M' sex FROM dual UNION ALL SELECT 'F' FROM dual) a
  LEFT OUTER JOIN test_tbl b
  PARTITION BY (b.code)
    ON a.sex = b.sex
 ORDER BY code, sex DESC
;
-- 2. Model --
SELECT *
  FROM test_tbl
 MODEL
 PARTITION BY (code)
 DIMENSION BY (sex)
 MEASURES (value)
 RULES (value[FOR sex IN ('M', 'F')] = NVL(value[cv()], '0000'))
 ORDER BY code, sex DESC
;
by 커피요쿠르트d [2013.02.14 14:30:00]
//제로 //손님 //마농님 

감사합니다.

파티션  OUTER JOIN(Partitioned Outer Join) 을 알게 되었네요. 
더불어 MODEL이라는 것도요.. 

위와 같이 자료가 들어가는 것을 희소(Sparse) 형태로 저장된다라는 것도 알게 되었습니다.

정말 감사드립니다. 

즐거운 오후 되셔요~~!!
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입