다중 row를
하나의 column으로 바꾸기
7/11 개발일기
개발하던 중 이런 문제를 만났다. 실제 사례를 예제로 바꾸어보자면 아래와 같다.
(1) 주문 테이블
select *
from order
------------------------------------------------
| 고객아이디 | 이름 | 주문번호 |
------------------------------------------------
| A0100001 | 나문희 | 0001 |
| A0200002 | 나영희 | 0002 |
| A0300003 | 나순희 | 0003 |
------------------------------------------------
(2) 주문 상세 테이블
select *
from order_detail
------------------------------------------------
| 고객아이디 | 주문번호 | 아이템 |
------------------------------------------------
| A0100001 | 0001 | 바지 |
| A0100001 | 0001 | 치마 |
| A0100001 | 0001 | 치마2 |
------------------------------------------------
(3) 실제 화면 출력 데이터
---------------------------------------------------------------------------
| 고객아이디 | 이름 | 주문번호 | 아이템 |
---------------------------------------------------------------------------
| A0100001 | 나문희 | 0001 | 바지, 치마, 치마2 |
---------------------------------------------------------------------------
기존에는 order과 order_detail 에서 각각 데이터를 출력하여, jsp 에서 for문을 중첩하여 수행하고 있었다. 그리고 반복문을 중첩 수행하면서 데이터를 뽑아내 "," 구분자로 print 하는 소스였다.
그런데 이것때문에 쿼리를 두개나 부르고 싶지 않았다. model 도 두개나 만들고 싶지 않았고.. 그래서 고민하다가 하나의 Query 로 해결할 수 있는 방법을 찾아봤다. 그러다가 아래의 함수를 발견했다.
LISTAGG(합칠 컬럼명, 구분자)
- ORACLE 11g r2 이상에서 가능
- 문자열을 하나로 합쳐준다
위 함수를 적용하여 예제를 풀면 이런 쿼리로 위 데이터 출력이 가능하다.
select customer_id, customer_name, order_id,
LISTAGG(item, ',') WITHIN GROUP (ORDER BY item) as ITEM
from order a, order_detail b
where a.order_id = b.order_id
and a.customer_id = b.customer_id
group by customer_id, customer_name, order_id
※group by 에는 group by 함수를 적용한 컬럼 외 다른 컬럼을 모두 기입해야 한다.
사실 이 예제는 decode, pivot 여러 함수를 거친 끝에 만난.. 귀한 함수다ㅠㅠ
decode와 pivot으로 한창 삽질을 한 끝에 LISTAGG 를 찾았다.
LISTAGG(ITEM, ',') 에서 ',' 는 구분자를 뜻한다. 여기에 '+' 라고 넣으면 아래와 같이 출력된다.
---------------------------------------------------------------------------
| 고객아이디 | 이름 | 주문번호 | 아이템 |
---------------------------------------------------------------------------
| A0100001 | 나문희 | 0001 | 바지+치마+치마2 |
---------------------------------------------------------------------------
