In this example, we are going to use the ;

  • LISTAGG
  • REGEXP_REPLACE

For a description of the LISTAGG function please read :Oracle LISTAGG

For understanding the function REGEXP_REPLACE ,I advice you to read Oracle REGEXP_REPLACE & Oracle Live SQL : REGEXP_REPLACE

Background :
I needed to create a report in Jaspersoft that would list all the “orders” on a “pallet_id” and since it contained duplicate ordernumbers I needed to use a LISTAGG in Oracle and return with distinct values
On Oracle there is no “listagg distinct” option available and that’s why I came up with the solution as provided below.

Side note:
I know there are many solutions to overcome the issue:
WM_CONCAT -> undocumented and not recomended by Oracle
LAG() analytic function & DECODE
….
……
Just keep in mind that LISTAGG returns a VARCHAR2 so you’re under a 4000 character limit.

Steps :
1) Copy and paste the JRXML code into Notepad++(or any other plain text editor)
Save the file as :  listagg_distinct.jrxml

JRXML

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Listagg_Distinct" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="431b7c40-428c-4890-910f-eabfb680fe69">
 <property name="ireport.zoom" value="2.0"/>
 <property name="ireport.x" value="0"/>
 <property name="ireport.y" value="419"/>
 <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Demo"/>
 <style name="LBL_RETURN_INSTRUCT_1" fontSize="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false">
 <conditionalStyle>
 <conditionExpression><![CDATA[$F{LBL_RETURN_INSTRUCT_1_1} != null]]></conditionExpression>
 <style forecolor="#000000" fontSize="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
 </conditionalStyle>
 </style>
 <queryString>
 <![CDATA[WITH ORDER_CONTAINER AS
(
SELECT 'COMPANYA' AS CLIENT_ID,'C043790475' AS CONSIGNMENT,'0106142568' AS ORDER_ID,'257084647200033232' AS CONTAINER_ID,'90000016' AS PALLET_ID,'42' AS PALLET_WEIGHT,'0.864' AS PALLET_VOLUME,'1.2' AS PALLET_DEPTH, '0.8' as PALLET_WIDTH, '0.9' AS PALLET_HEIGHT,'EURO' AS CONFIG_ID,'EURO' AS PALLET_GROUP FROM dual UNION ALL
SELECT 'COMPANYA' AS CLIENT_ID,'C043790475' AS CONSIGNMENT,'0106142854' AS ORDER_ID,'257084647200035175' AS CONTAINER_ID,'90000016' AS PALLET_ID,'42' AS PALLET_WEIGHT,'0.864' AS PALLET_VOLUME,'1.2' AS PALLET_DEPTH, '0.8' as PALLET_WIDTH, '0.9' AS PALLET_HEIGHT,'EURO' AS CONFIG_ID,'EURO' AS PALLET_GROUP FROM dual UNION ALL
SELECT 'COMPANYA' AS CLIENT_ID,'C043790475' AS CONSIGNMENT,'0106142854' AS ORDER_ID,'257084647200035168' AS CONTAINER_ID,'90000016' AS PALLET_ID,'42' AS PALLET_WEIGHT,'0.864' AS PALLET_VOLUME,'1.2' AS PALLET_DEPTH, '0.8' as PALLET_WIDTH, '0.9' AS PALLET_HEIGHT,'EURO' AS CONFIG_ID,'EURO' AS PALLET_GROUP FROM dual UNION ALL
SELECT 'COMPANYA' AS CLIENT_ID,'C043790475' AS CONSIGNMENT,'0106142568' AS ORDER_ID,'257084647200033249' AS CONTAINER_ID,'90000016' AS PALLET_ID,'42' AS PALLET_WEIGHT,'0.864' AS PALLET_VOLUME,'1.2' AS PALLET_DEPTH, '0.8' as PALLET_WIDTH, '0.9' AS PALLET_HEIGHT,'EURO' AS CONFIG_ID,'EURO' AS PALLET_GROUP FROM dual UNION ALL
SELECT 'COMPANYA' AS CLIENT_ID,'C043790475' AS CONSIGNMENT,'0106142854' AS ORDER_ID,'257084647200035182' AS CONTAINER_ID,'90000016' AS PALLET_ID,'42' AS PALLET_WEIGHT,'0.864' AS PALLET_VOLUME,'1.2' AS PALLET_DEPTH, '0.8' as PALLET_WIDTH, '0.9' AS PALLET_HEIGHT,'EURO' AS CONFIG_ID,'EURO' AS PALLET_GROUP FROM dual UNION ALL
SELECT 'COMPANYA' AS CLIENT_ID,'C043790475' AS CONSIGNMENT,'0106142568' AS ORDER_ID,'257084647200033225' AS CONTAINER_ID,'90000016' AS PALLET_ID,'42' AS PALLET_WEIGHT,'0.864' AS PALLET_VOLUME,'1.2' AS PALLET_DEPTH, '0.8' as PALLET_WIDTH, '0.9' AS PALLET_HEIGHT,'EURO' AS CONFIG_ID,'EURO' AS PALLET_GROUP FROM dual UNION ALL
SELECT 'COMPANYA' AS CLIENT_ID,'C043790475' AS CONSIGNMENT,'0106142568' AS ORDER_ID,'257084647200033218' AS CONTAINER_ID,'90000016' AS PALLET_ID,'42' AS PALLET_WEIGHT,'0.864' AS PALLET_VOLUME,'1.2' AS PALLET_DEPTH, '0.8' as PALLET_WIDTH, '0.9' AS PALLET_HEIGHT,'EURO' AS CONFIG_ID,'EURO' AS PALLET_GROUP FROM dual
),
STRINGS AS
(
SELECT '0106142568, 0106142568, 0106142568, 0106142568, 0106142854, 0106142854, 0106142854' s FROM DUAL
),
ORDER_DATA AS
( SELECT 'COMPANYA' AS CLIENT_ID,'05-10-2016 12:47' AS PRINT_DATE,'C043790475' AS CONSIGNMENT,'FULFILLMENT COMPANY' AS NAME,'Mantualaan 115' AS ADDRESS1,'5632 RC Eindhoven' AS PCTOWN,'PARCEL-DIRECT' AS CARRIER_ID FROM DUAL
)

SELECT DISTINCT
OD.CLIENT_ID,
OD.PRINT_DATE,
OD.CONSIGNMENT,
OD.NAME,
OD.ADDRESS1,
OD.PCTOWN,
OD.CARRIER_ID,
OC.PALLET_ID,
OC.PALLET_WEIGHT,
OC.PALLET_VOLUME,
OC.PALLET_DEPTH,
OC.PALLET_WIDTH,
OC.PALLET_HEIGHT,
OC.CONFIG_ID,
OC.PALLET_GROUP,
(Select COUNT(DISTINCT OC1."CONTAINER_ID") from ORDER_CONTAINER OC1 where OC1.consignment=OD.CONSIGNMENT ) AS CARTON_QTY,
(Select listagg(OC1."ORDER_ID", ', ') within group (order by OC1."PALLET_ID" ASC)
 from ORDER_CONTAINER OC1 where OC1.consignment=OD.CONSIGNMENT ) AS LIST_AGG,
(Select replace(regexp_replace(regexp_replace(listagg(OC1."ORDER_ID", ', ') within group (order by OC1."PALLET_ID" ASC),',\s*',',') ,'([^,]+)(,\1)+', '\1'),',',',')
 from ORDER_CONTAINER OC1 where OC1.consignment=OD.CONSIGNMENT ) AS LIST_AGG_DISTINCT,
s "Original String",
regexp_replace(s,',\s*',',') AS "String_no_blanks",
regexp_replace(regexp_replace(s,',\s*',',') ,'([^,]+)(,\1)+', '\1') AS "STRING_No_Duplicate",
replace(regexp_replace(regexp_replace(s,',\s*',',') ,'([^,]+)(,\1)+', '\1') ,',',', ') AS "STRING_No_Dupl_with_blanks",
regexp_replace(s,'([^,]+)(,\1)+', '\1') AS STRING_Wrong

FROM STRINGS,ORDER_DATA OD
 LEFT OUTER JOIN ORDER_CONTAINER OC
 ON OD.CLIENT_ID=OC.CLIENT_ID
 AND OD.CONSIGNMENT=OC.CONSIGNMENT]]>
 </queryString>
 <field name="CLIENT_ID" class="java.lang.String"/>
 <field name="PRINT_DATE" class="java.lang.String"/>
 <field name="CONSIGNMENT" class="java.lang.String"/>
 <field name="NAME" class="java.lang.String"/>
 <field name="ADDRESS1" class="java.lang.String"/>
 <field name="PCTOWN" class="java.lang.String"/>
 <field name="CARRIER_ID" class="java.lang.String"/>
 <field name="PALLET_ID" class="java.lang.String"/>
 <field name="PALLET_WEIGHT" class="java.lang.String"/>
 <field name="PALLET_VOLUME" class="java.lang.String"/>
 <field name="PALLET_DEPTH" class="java.lang.String"/>
 <field name="PALLET_WIDTH" class="java.lang.String"/>
 <field name="PALLET_HEIGHT" class="java.lang.String"/>
 <field name="CONFIG_ID" class="java.lang.String"/>
 <field name="PALLET_GROUP" class="java.lang.String"/>
 <field name="CARTON_QTY" class="java.math.BigDecimal"/>
 <field name="LIST_AGG" class="java.lang.String"/>
 <field name="LIST_AGG_DISTINCT" class="java.lang.String"/>
 <field name="Original String" class="java.lang.String"/>
 <field name="String_no_blanks" class="java.lang.String"/>
 <field name="STRING_No_Duplicate" class="java.lang.String"/>
 <field name="STRING_No_Dupl_with_blanks" class="java.lang.String"/>
 <field name="STRING_WRONG" class="java.lang.String"/>
 <pageHeader>
 <band height="182" splitType="Stretch">
 <textField isBlankWhenNull="true">
 <reportElement uuid="4479c115-1cf3-45d4-82ad-2b07afa23815" x="337" y="0" width="216" height="20"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{PRINT_DATE}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="d3d0d2b4-32ff-4f6a-b9fe-c207c4dc5754" x="274" y="25" width="80" height="20"/>
 <textElement>
 <font isBold="true"/>
 </textElement>
 <text><![CDATA[Consignment:]]></text>
 </staticText>
 <textField isBlankWhenNull="true">
 <reportElement uuid="bd933cb9-4913-474f-a5cc-d2591bd79d93" x="354" y="25" width="199" height="20"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{CONSIGNMENT}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="e3e2dabb-e386-438a-9967-6e01cd225b50" x="5" y="54" width="100" height="20"/>
 <textElement/>
 <text><![CDATA[Carrier:]]></text>
 </staticText>
 <textField isBlankWhenNull="true">
 <reportElement uuid="976b6666-f1bd-4356-ad67-de04c47f286c" x="105" y="54" width="249" height="20"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{CARRIER_ID}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="d6763911-01dc-42b5-a616-c2d008c1b80a" x="5" y="74" width="100" height="20"/>
 <textElement/>
 <text><![CDATA[Customer:]]></text>
 </staticText>
 <textField isBlankWhenNull="true">
 <reportElement uuid="1794f974-15fe-4d48-9375-2f57dcf5035c" x="105" y="74" width="249" height="20"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{NAME}]]></textFieldExpression>
 </textField>
 <textField isBlankWhenNull="true">
 <reportElement uuid="61bdfc16-2a50-4b7d-b61a-bbb8fbc3e7bf" x="105" y="94" width="249" height="20"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{ADDRESS1}]]></textFieldExpression>
 </textField>
 <textField isBlankWhenNull="true">
 <reportElement uuid="5cb344e8-a51b-4ddf-83d2-55040d05d899" x="105" y="114" width="249" height="20"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{PCTOWN}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="5ca57660-4008-4afe-b655-06981e46d050" x="3" y="167" width="100" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement>
 <paragraph leftIndent="2"/>
 </textElement>
 <text><![CDATA[Pallet ID]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="493af22b-5c08-4921-9b50-73c9a85e7182" x="103" y="167" width="80" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="0.0"/>
 </box>
 <textElement>
 <paragraph leftIndent="5"/>
 </textElement>
 <text><![CDATA[Group]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="dbd00ecb-8251-43da-8691-e41703e610ed" x="183" y="167" width="50" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="0.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement/>
 <text><![CDATA[Type]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="63160b4c-d0ec-44b6-98da-ed9d8d51878c" x="103" y="153" width="130" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[Package]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="bb8657db-2f44-4699-b13b-954d5ffc9fcf" x="316" y="167" width="50" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="0.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[H]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="ce7363f7-b038-4a1f-8b08-9b6e5c18777b" x="268" y="167" width="48" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="0.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="0.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[W ]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="d70eb2bd-b290-47e2-a30a-4ca840cb0a52" x="233" y="167" width="35" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="0.0"/>
 </box>
 <textElement>
 <paragraph firstLineIndent="5"/>
 </textElement>
 <text><![CDATA[L]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="f3c20e24-0b65-40fe-a006-32cee44a73f4" x="366" y="167" width="57" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[(M3)]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="8c835ff4-ced9-44cf-ad00-4727dcb06b8a" x="366" y="153" width="57" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[Volume]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="aa0ef100-23ea-44fa-a9b1-2c2868f300ff" x="233" y="153" width="133" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[Dimensions]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="85d57c14-cc65-4418-b903-dab1773d6172" x="423" y="167" width="57" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[(KG)]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="72f85b1d-3b82-4f2e-8f8d-554618d3855d" x="423" y="153" width="57" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[Weight]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="dc396560-1315-429a-9024-55de886abad3" x="481" y="167" width="51" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[QTY]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="4c0017e0-740b-484c-a189-5a7a91d79ec7" x="481" y="153" width="51" height="14"/>
 <box>
 <topPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement textAlignment="Center"/>
 <text><![CDATA[Cartons]]></text>
 </staticText>
 </band>
 </pageHeader>
 <detail>
 <band height="389" splitType="Stretch">
 <textField isBlankWhenNull="true">
 <reportElement uuid="c2c9df48-2afb-4d80-bfc7-02b4ea333fdd" x="3" y="0" width="100" height="14"/>
 <textElement>
 <paragraph leftIndent="2"/>
 </textElement>
 <textFieldExpression><![CDATA[$F{PALLET_ID}]]></textFieldExpression>
 </textField>
 <textField isBlankWhenNull="true">
 <reportElement uuid="345bd6a3-ec41-44b2-b1de-0b1ee83762f6" x="103" y="0" width="80" height="14"/>
 <textElement>
 <paragraph leftIndent="5"/>
 </textElement>
 <textFieldExpression><![CDATA[$F{PALLET_GROUP}]]></textFieldExpression>
 </textField>
 <textField isBlankWhenNull="true">
 <reportElement uuid="ddb5041f-ca1a-4824-bf2f-6d8b4fcace73" x="183" y="0" width="50" height="14"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{CONFIG_ID}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="60a86c83-2648-43a7-8eae-fac8aa0b05a9" x="106" y="29" width="127" height="14"/>
 <textElement>
 <font size="10" isItalic="false"/>
 </textElement>
 <text><![CDATA[Orders on pallet :]]></text>
 </staticText>
 <textField isBlankWhenNull="true">
 <reportElement uuid="3c1af71c-7598-4070-9ac5-18f1e96bbe0a" x="233" y="0" width="133" height="14">
 <printWhenExpression><![CDATA[Boolean.valueOf($F{PALLET_DEPTH} != null)]]></printWhenExpression>
 </reportElement>
 <textElement>
 <paragraph firstLineIndent="5"/>
 </textElement>
 <textFieldExpression><![CDATA[$F{PALLET_DEPTH}+ " x " + $F{PALLET_WIDTH}+ " x " + $F{PALLET_HEIGHT}]]></textFieldExpression>
 </textField>
 <textField isBlankWhenNull="true">
 <reportElement uuid="48cdb1f6-04d3-4b30-9b97-6e78e7c41337" x="366" y="0" width="57" height="14"/>
 <textElement textAlignment="Center"/>
 <textFieldExpression><![CDATA[$F{PALLET_VOLUME}]]></textFieldExpression>
 </textField>
 <textField isBlankWhenNull="true">
 <reportElement uuid="ff06abd1-b141-4904-9601-a10660150a7d" x="423" y="0" width="57" height="14"/>
 <textElement textAlignment="Center"/>
 <textFieldExpression><![CDATA[$F{PALLET_WEIGHT}]]></textFieldExpression>
 </textField>
 <textField isBlankWhenNull="true">
 <reportElement uuid="a6cb3f73-a63e-434b-bdc5-6d92f83734bd" x="481" y="0" width="51" height="14"/>
 <textElement textAlignment="Center"/>
 <textFieldExpression><![CDATA[$F{CARTON_QTY}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="c3dc9dcf-2cf8-43b6-a81a-5eef99279b01" x="0" y="43" width="103" height="20"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement>
 <paragraph leftIndent="2"/>
 </textElement>
 <text><![CDATA[LISTAGG]]></text>
 </staticText>
 <textField>
 <reportElement uuid="10800855-4ead-45ca-baa2-b060ffd57942" x="105" y="43" width="450" height="20"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{LIST_AGG}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="9919005c-ea3b-4725-8b81-5e9dafe3e0de" x="0" y="63" width="103" height="20"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement>
 <paragraph leftIndent="2"/>
 </textElement>
 <text><![CDATA[LISTAGG_DISTINCT]]></text>
 </staticText>
 <textField>
 <reportElement uuid="26dda1b9-2383-46c0-a74e-912a79dda123" x="105" y="63" width="450" height="20"/>
 <textElement/>
 <textFieldExpression><![CDATA[$F{LIST_AGG_DISTINCT}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="0997c144-cf96-4ead-af28-0e48e0e9b59b" x="0" y="119" width="553" height="146"/>
 <box>
 <topPen lineWidth="1.0"/>
 <leftPen lineWidth="1.0"/>
 <bottomPen lineWidth="1.0"/>
 <rightPen lineWidth="1.0"/>
 </box>
 <textElement>
 <paragraph leftIndent="2"/>
 </textElement>
 <text><![CDATA[WITH strings AS
(
SELECT '0106142568, 0106142568, 0106142568, 0106142568, 0106142854, 0106142854, 0106142854' s from DUAL
)
 SELECT
 s "Original String",
 regexp_replace(s,',\s*',',') AS "String_no_blanks",
 regexp_replace(regexp_replace(s,',\s*',',') ,'([^,]+)(,\1)+', '\1') AS "STRING_No_Duplicate",
 replace(regexp_replace(regexp_replace(s,',\s*',',') ,'([^,]+)(,\1)+', '\1') ,',',', ') AS "STRING_No_Dupl_with_blanks",
 regexp_replace(s,'([^,]+)(,\1)+', '\1') AS STRING_Wrong
 FROM strings ]]></text>
 </staticText>
 <line>
 <reportElement uuid="67922321-daf4-44ff-8e81-38ac46376055" x="1" y="99" width="552" height="1"/>
 </line>
 <staticText>
 <reportElement uuid="62dcef83-64e9-4825-bcaa-c1b4837ca509" x="207" y="100" width="80" height="20"/>
 <textElement>
 <font isBold="true"/>
 </textElement>
 <text><![CDATA[Code Explained ]]></text>
 </staticText>
 <staticText>
 <reportElement uuid="84c998bd-cf2e-4a1b-80e8-617eabf36ce0" x="1" y="297" width="100" height="20"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <text><![CDATA[String no blanks]]></text>
 </staticText>
 <textField>
 <reportElement uuid="2527805b-fefb-476a-a147-6af1eb818060" x="101" y="297" width="450" height="20"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <textFieldExpression><![CDATA[$F{String_no_blanks}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="a991ad12-c559-43c6-85d0-fb3682c335ae" x="1" y="317" width="100" height="20"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <text><![CDATA[String no duplicate]]></text>
 </staticText>
 <textField>
 <reportElement uuid="4b249f62-4707-41ed-8d47-9c62194bb893" x="101" y="317" width="450" height="20"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <textFieldExpression><![CDATA[$F{STRING_No_Duplicate}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="725d6e66-60c6-43e0-b105-f9fe0405e4a4" x="1" y="337" width="100" height="28"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <text><![CDATA[String no duplicate with blanks]]></text>
 </staticText>
 <textField>
 <reportElement uuid="f81c3382-89c4-43bd-9cc3-549073c1a69c" x="101" y="337" width="450" height="20"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <textFieldExpression><![CDATA[$F{STRING_No_Dupl_with_blanks}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="c5eff991-d88a-4050-82f8-79482f93bd86" x="1" y="365" width="100" height="24" forecolor="#FF3300"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <text><![CDATA[String when not removing blanks first
]]></text>
 </staticText>
 <textField>
 <reportElement uuid="4187ff3a-5c10-4c9d-bbeb-6bb666324839" x="101" y="365" width="450" height="20" forecolor="#FF3300"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <textFieldExpression><![CDATA[$F{STRING_WRONG}]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement uuid="0fd30a6c-7477-40c6-a726-72f02a0f682d" x="1" y="277" width="100" height="20"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <text><![CDATA[Original String]]></text>
 </staticText>
 <textField>
 <reportElement uuid="a5d78115-7055-4dba-b1bd-97b189cba8e9" x="101" y="277" width="452" height="20"/>
 <textElement>
 <font size="9"/>
 </textElement>
 <textFieldExpression><![CDATA[$F{Original String}]]></textFieldExpression>
 </textField>
 </band>
 </detail>
</jasperReport>


 
 
 
 

 

2) Loading the page in iReport of Jaspersoft Studio would give you:
listagg_regexp_replace2-jaspersoft

When you preview that data should be presented as below.
listagg_regexp_replace.jaspersoft.PNG
Let me explain how I achieved all this.

LISTAGG:
Using the regular LISTAGG function  ;

(Select listagg(OC1."ORDER_ID", ', ') within group (order by OC1."PALLET_ID" ASC)
 from ORDER_CONTAINER OC1 where OC1.consignment=OD.CONSIGNMENT ) AS LIST_AGG,

The result is a long string and In this used example contains duplicates:

0106142568, 0106142568, 0106142568, 0106142568, 0106142854, 0106142854, 0106142854

LISTAGG “DISTINCT”:
using the regular LISTAGG function in combination with REGEXP and REPLACE we can easily return unique distinct values.

(Select replace(regexp_replace(regexp_replace(listagg(OC1."ORDER_ID", ', ') within group (order by OC1."PALLET_ID" ASC),',\s*',',') ,'([^,]+)(,\1)+', '\1'),',',', ')
 from ORDER_CONTAINER OC1 where OC1.consignment=OD.CONSIGNMENT ) AS LIST_AGG_DISTINCT,

Understanding what the code does

Let’s break up the whole string into sections and see what the result is.
For understanding the function REGEXP_REPLACE I advice you to read Oracle REGEXP_REPLACE & Oracle Live SQL : REGEXP_REPLACE

replace(regexp_replace(regexp_replace(listagg(OC1.”ORDER_ID”, ‘, ‘) within group (order by OC1.”PALLET_ID” ASC),’,\s*’,’,’) ,'([^,]+)(,\1)+’, ‘\1’),’,’,’, ‘)

  1. regexp_replace(,’,\s*’,’,’) : Removes the blanks if there is one.
  2. regexp_replace(regexp_replace(,’,\s*’,’,’) ,'([^,]+)(,\1)+’, ‘\1’) : Removes the duplicates.
  3. replace(regexp_replace(regexp_replace(s,’,\s*’,’,’) ,'([^,]+)(,\1)+’, ‘\1’) ,’,’,’, ‘):  Adds a “space/blank” between de distinct values.

And yes you really need to remove first the blanks and then remove the duplicates.

See example code below, this will give you a better idea what each line of code returns back.

WITH strings AS ( 
SELECT '0106142568, 0106142568, 0106142568, 0106142568, 0106142854, 0106142854, 0106142854' s from DUAL
) 
 SELECT 
 s "Original String",
 regexp_replace(s,',\s*',',') AS "String_no_blanks",
 regexp_replace(regexp_replace(s,',\s*',',') ,'([^,]+)(,\1)+', '\1') AS "STRING_No_Duplicate",
 replace(regexp_replace(regexp_replace(s,',\s*',',') ,'([^,]+)(,\1)+', '\1') ,',',', ') AS "STRING_No_Dupl_with_blanks",
 regexp_replace(s,'([^,]+)(,\1)+', '\1') AS STRING_Wrong
 FROM strings

Feel free to share if you found it usefully.

Kind regards
Martijn Bergevoet

Advertisements