For XML Path Equivalent in Oracle For String Aggregation

During a migration from SQL Server to Oracle 11g, I’ve came across with a specific functionality of T-SQL FOR XML PATH.
In the scope of the migration, the result of the select clause where this was applied was the concatenation of the values of a column per key.

Here’s an example of what I’ve found on T-SQL during the migration:

Declare @tbl1 table (
  id int,
  Col1 varchar(50),
  Col2 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'A001','Y'

INSERT INTO @tbl1
SELECT 2,'A002','N'

INSERT INTO @tbl1
SELECT 3,'A003','N'

INSERT INTO @tbl1
SELECT 4,'A004','Y'

INSERT INTO @tbl1
SELECT 5,'A005','N'

INSERT INTO @tbl1
SELECT 6,'A006','N'

INSERT INTO @tbl1
SELECT 7,'A007','N'

INSERT INTO @tbl1
SELECT 8,'A008','Y'

-- Show current values
select * from @tbl1

-- Get all values when col2 has value 'N'
SELECT c.col1 AS [text()]
FROM @tbl1 c
WHERE c.col2 = 'N'
FOR XML PATH(''),type

-- Aggregate all values from col1 using col2 as key
select c1.col2 [Yes/No],
  (SELECT c.col1 AS [text()]
   FROM @tbl1 c
   WHERE c.col2 = c1.col2
   FOR XML PATH(''),type) as Value
FROM @tbl1 c1
group by c1.col2
order by c1.col2

The execution of this T-SQL script shows how it is possible transform the result of a query into a new result where the values are aggregated by col1, and all col2 values are concatenated.

In order to achieve this kind of aggregate transformation in Oracle, one has to make use of an associative array, as Tom explains in the “String Concatenation” how one can do this. Using the examples in the AskTom web site, first we create the “STRING_AGG_TYPE” type:

CREATE OR REPLACE TYPE string_agg_type AS object
(
  total VARCHAR2(4000),

  static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type) RETURN NUMBER,

  member FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, VALUE IN VARCHAR2) RETURN NUMBER,

  member FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,

  member FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) RETURN NUMBER
);
/

And the correspondent body:

CREATE OR REPLACE TYPE BODY string_agg_type
IS

static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
RETURN NUMBER
IS
BEGIN
  sctx := string_agg_type( NULL );
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, VALUE IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
  -- NOTE: Insert a separator in the empty string if you wish
  self.total := self.total || '' || VALUE;
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
  -- NOTE: ltrim(self.total, '') if you've specified a separator in ODCIAggregateIterate
  returnValue := LTRIM(self.total);
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
RETURN NUMBER
IS
BEGIN
  self.total := self.total || ctx2.total;
  RETURN ODCIConst.Success;
END;

END;
/

Then we create the aggregated function “stragg” of the type we’ve just created:

CREATE OR REPLACE FUNCTION stragg(input VARCHAR2 ) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

We are now able to make a similar aggregation in Oracle:

CREATE TABLE tbl1 (
  id NUMBER(10),
  Col1 VARCHAR(50),
  Col2 VARCHAR(50)
);
/

INSERT INTO tbl1 (id, col1, col2)
VALUES (1,'A001','Y');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (2,'A002','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (3,'A003','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (4,'A004','Y');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (5,'A005','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (6,'A006','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (7,'A007','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (8,'A008','Y');
/
COMMIT;
/

-- Show current values
SELECT * FROM tbl1;
/

-- Get all values when col2 has value 'N'
SELECT c.col2, STRAGG(c.col1)
FROM tbl1 c
WHERE c.col2 = 'N'
GROUP BY c.col2;
/

-- Aggregate all values from col1 using col2 as key
SELECT c1.col2 "Yes/No",
  (SELECT STRAGG(c.col1)
   FROM tbl1 c
   WHERE c.col2 = c1.col2
  ) AS val
FROM tbl1 c1
GROUP BY c1.col2
ORDER BY c1.col2;
/

-- drop table tbl1;

And that’s it. This will work in 9i and beyond, for 8i, check the 8i workaround in the same AskTom thread.