数据透视不适用于聚合clob列

Question

我有类似以下的数据

ID   COUNT  COL1    COL_CLOB
12345   5      a     (HUGE_CLOB)
12345   8      b     (HUGE_CLOB)

我想要像下面这样的输出

ID   a_count   a_COL_CLOB  b_COUNT b_COL_CLOB
12345   5      (HUGE_CLOB)     8    (HUGE_CLOB)

我在查询中使用xmlagg，因为COL_CLOB的大小超过4000个字符，而listagg不支持这一点，pivot无法使用聚合CLOB列。请告知

Answer 1

您可以定义自己的函数来聚合CLOB值：

CREATE OR REPLACE TYPE CLOBAggregation AS OBJECT(
value CLOB,
STATIC FUNCTION ODCIAggregateInitialize(
ctx         IN OUT CLOBAggregation
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(
self        IN OUT CLOBAggregation,
value       IN     CLOB
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(
self        IN OUT CLOBAggregation,
returnValue    OUT CLOB,
flags       IN     NUMBER
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(
self        IN OUT CLOBAggregation,
ctx         IN OUT CLOBAggregation
) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY CLOBAggregation
IS
STATIC FUNCTION ODCIAggregateInitialize(
ctx         IN OUT CLOBAggregation
) RETURN NUMBER
IS
BEGIN
ctx := CLOBAggregation( NULL );
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateIterate(
self        IN OUT CLOBAggregation,
value       IN     CLOB
) RETURN NUMBER
IS
BEGIN
IF value IS NULL THEN
NULL;
ELSIF self.value IS NULL THEN
self.value := value;
ELSE
self.value := self.value || ',' || value;
END IF;
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateTerminate(
self        IN OUT CLOBAggregation,
returnValue    OUT CLOB,
flags       IN     NUMBER
) RETURN NUMBER
IS
BEGIN
returnValue := self.value;
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateMerge(
self        IN OUT CLOBAggregation,
ctx         IN OUT CLOBAggregation
) RETURN NUMBER
IS
BEGIN
IF self.value IS NULL THEN
self.value := ctx.value;
ELSIF ctx.value IS NULL THEN
NULL;
ELSE
self.value := self.value || ',' || ctx.value;
END IF;
RETURN ODCIConst.SUCCESS;
END;
END;
/
CREATE FUNCTION CLOB_AGG( value CLOB )
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING CLOBAggregation;
/

然后，对于样本数据：

CREATE TABLE table_name ( id NUMBER, "COUNT" NUMBER, col1 VARCHAR2(5), col_clob CLOB );
DECLARE
p_clob CLOB;
BEGIN
p_clob := EMPTY_CLOB() || LPAD( 'a', 4000, 'a' ) || LPAD( 'a', 10, 'a' );
INSERT INTO table_name VALUES ( 12345, 5, 'a', p_clob );
p_clob := EMPTY_CLOB() || LPAD( 'b', 4000, 'b' ) || LPAD( 'b', 10, 'b' );
INSERT INTO table_name VALUES ( 12345, 8, 'b', p_clob );
END;
/

然后可以使用条件聚合：

SELECT id,
MAX( CASE col1 WHEN 'a' THEN "COUNT"  END ) AS a_count,
CLOB_AGG( CASE col1 WHEN 'a' THEN col_clob END ) AS a_col_clob,
MAX( CASE col1 WHEN 'b' THEN "COUNT"  END ) AS b_count,
CLOB_AGG( CASE col1 WHEN 'b' THEN col_clob END ) AS b_col_clob
FROM   table_name
GROUP BY id

哪个输出：

ID|A_COUNT|A_COL_CLOB|B_COUNT|B_COL_CLOB----：|------：|：--------------------|------：：|：-------------------12345|5|aaaaa<4000多>aaaaa|8|bbbbb<4000多>bbbbb

db<gt；小提琴这里