데이터베이스 질문과 답변
쿼리만으로 JOIN한 테이블의 여러행 결과값을 한줄로 보여줄수 있나요?
1
2
2,042
by c.jun [2015.10.08 12:19:17]
안녕하세요.
쿼리만으로 JOIN한 테이블의 여러행 결과값을 한줄로 보여줄수 있나요?
예를 들면,
[TABLE A]
ID / ORGN
1 / 안녕
2 / 잘가
[TABLE B]
ID / TRSN
1 / HI
1 / HELLO
1 / GOOD MORNING
2 / BYE
2 / GOOD BYE
이렇게 있다면 한 행에 ID 하나에 TITLE 과 TEXT 모두 보고싶은거죠.
ID / ORGN / TRSN
1 / 안녕 / HI, HELLO, GOOD MORNING
2 / 잘가 / BYE, GOOD BYE
쿼리만으로도 결과값을 이렇게 뿌려줄 수 있을까요?
by 창조의날개
[2015.10.08 13:25:23]
WITH TABLEA(ID , ORGN) AS(
SELECT 1 , '안녕' FROM DUAL UNION ALL
SELECT 2 , '잘가' FROM DUAL
)
, TABLEB(ID , TRSN) AS(
SELECT 1 , 'HI' FROM DUAL UNION ALL
SELECT 1 , 'HELLO' FROM DUAL UNION ALL
SELECT 1 , 'GOOD MORNING' FROM DUAL UNION ALL
SELECT 2 , 'BYE' FROM DUAL UNION ALL
SELECT 2 , 'GOOD BYE' FROM DUAL
)
SELECT AA.ID, AA.ORGN, BB.TRSN
FROM TABLEA AA
, (SELECT ID
, SUBSTR(XMLAgg(XMLElement(x, ', ', TRSN) ORDER BY ROWNUM).Extract('//text()'), 2) TRSN
FROM TABLEB
GROUP BY ID
) BB
WHERE AA.ID = BB.ID
;
http://www.gurubee.net/article/55512
by 박세웅
[2015.10.08 13:29:38]
WITH A AS
(SELECT 1 IDX ,'안녕' ORG FROM DUAL
UNION ALL
SELECT 2 ,'잘가' FROM DUAL)
, B AS
(SELECT 1 IDX ,'HI' ORG FROM DUAL
UNION ALL
SELECT 1 ,'HELLO' FROM DUAL
UNION ALL
SELECT 1 ,'GOOD MORNING' FROM DUAL
UNION ALL
SELECT 2 ,'BYE' FROM DUAL
UNION ALL
SELECT 2 ,'GOOD BYE' FROM DUAL)
SELECT A.IDX
, A.ORG
, LISTAGG(B.ORG,',') WITHIN GROUP (ORDER BY ROWNUM)
FROM A A, B B
WHERE A.IDX = B.IDX
GROUP BY A.IDX , A.ORG
ORDER BY 1
) 버튼을 클릭하여 작성 하시면 됩니다.