(Oracle) LISTAGG 함수 사용법 (with Partition By)

 

The LISTAGG syntax is:

LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

 

LISTAGG Parameters
There are several mandatory and optional parameters in the LISTAGG function. The parameters of the LISTAGG function are:

measure_expr (mandatory): This is the column (or expression) that you wish to concatenate the values of. In the example above, I used last_name.
delimiter (optional): This is the delimiter, or the character in between each value of measure_expr. If this is not specified, no value is used.
order_by_clause (mandatory): This is the order of the concatenated values in measure_expr are listed.
query_partition_clause (optional): This allows you to use LISTAGG as an analytic function, which we will see in the examples section below.

 

LISTAGG 함수는 가끔 사용했었는데, Partition 절을 함께 사용하는 방법은 최근에 알게 되어 유용하게 사용했습니다.

SELECT T1.*
      ,LISTAGG(PRDT_CD, ',') WITHIN GROUP(ORDER BY PRDT_CD) OVER(PARTITION BY ORN_ID, ORN_PRDT_NO) AS PRDT_CD_S
FROM TT_TEST T1

 

---