아래와 같은 데이터가 있는데요..
이런 형태로 뽑으려면 어떻게 해야할까요 ㅠㅠ;
사용 DB는 오라클 9i입니다.
| 이름 | 과일 |
| 홍길동 | 사과 |
| 갑순 | 배 |
| 갑순 | 귤 |
| 갑돌 | 사과 |
| 갑돌 | 배 |
| 갑돌 | 귤 |
이런 형태로 뽑으려면 어떻게 해야할까요 ㅠㅠ;
| col1 | col2 |
| 홍길동 | 사과 |
| 갑순 | 배,귤 |
| 갑돌 | 사과,배,귤 |
사용 DB는 오라클 9i입니다.
Comment 3
-
건우아빠
2013.08.21 14:03
-
건우아빠
2013.08.21 14:28
따라 해보니 되네요.... ㅎ
WITH RES AS (
SELECT '홍길동' AS col1, '사과' AS col2 FROM dual UNION ALL
SELECT '갑순' AS col1, '배' AS col2 FROM dual UNION ALL
SELECT '갑순' AS col1, '귤' AS col2 FROM dual UNION ALL
SELECT '갑돌' AS col1, '사과' AS col2 FROM dual UNION ALL
SELECT '갑돌' AS col1, '배' AS col2 FROM dual UNION ALL
SELECT '갑돌' AS col1, '귤' AS col2 FROM dual
) ,RES1 as
(
SELECT a.*
, row_number() OVER(PARTITION BY col1 ORDER BY col2) AS rn
FROM RES a
)SELECT col1
, SUBSTR(max(rpad(level,5) || SYS_CONNECT_BY_PATH(col2,',')),7) as col2
FROM RES1
CONNECT BY PRIOR rn = rn - 1
AND PRIOR col1 = col1
GROUP BY col1
ORDER BY col1; -
unyo
2013.08.21 15:08
답변 감사합니다. 정보 검색능력이 많이 부족하네요 ㅠ_ㅠ;;
http://kin.naver.com/qna/detail.nhn?d1id=1&dirId=10205&docId=64484019&qb=7Jik65287YG0IOq1rOu2hOyekCDrtpnsnbTquLA=&enc=utf8§ion=kin&rank=1&search_sort=0&spq=0&pid=Rme50U5Y7tlssaPpeh8sssssstN-172380&sid=UhRJvXJvLDAAAHslFvE
참고 하시면되지 않을까요....