In Oracle, you can use the GROUP_CONCAT() function and the GROUP BY clause to concatenate column values from multiple rows into a single string.
The GROUP_CONCAT() function is not a built-in function in Oracle, but you can use the following ways to concatenate the column values :
1. Using the LISTAGG function:
SELECT CUSTOMER_ID, LISTAGG(PRODUCT, ', ') WITHIN GROUP (ORDER BY PRODUCT) AS PRODUCTS
FROM ORDERS
GROUP BY CUSTOMER_ID;
This query will group the orders by customer_id, and for each group, it will concatenate the product column with a comma separator using the LISTAGG() function.
2. Using the WM_CONCAT function:
SELECT CUSTOMER_ID, WM_CONCAT(PRODUCT) as PRODUCTS
FROM ORDERS
GROUP BY CUSTOMER_ID;
This query will group the orders by customer_id and for each group it will concatenate the product column using the WM_CONCAT() function.
3. Using the SYS_CONNECT_BY_PATH function:
SELECT CUSTOMER_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRODUCT, ','))
KEEP (DENSE_RANK LAST ORDER BY curr), ',') AS PRODUCTS
FROM (SELECT CUSTOMER_ID, PRODUCT,
ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY PRODUCT) AS curr,
ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY PRODUCT) -1 AS prev
FROM ORDERS)
WHERE curr = 1
GROUP BY CUSTOMER_ID
CONNECT BY prev = PRIOR curr AND CUSTOMER_ID = PRIOR CUSTOMER_ID
START WITH curr = 1;
This query will group the orders by customer_id, and for each group, it will concatenate the product column using the SYS_CONNECT_BY_PATH() function.
It's important to note that the first method is the most standard and recommended method for concatenation in Oracle. The other methods are alternatives to work around the unavailability of GROUP_CONCAT in Oracle.
0 Komentar