您可以定义自己的函数来聚合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 more>aaaaa |       8 | bbbb<4000 more>bbbbb

• db<>fiddle here*

也许它不是那么优雅，但它有时对我有用，当我面对CLOB列，超过4000个标志。最大的问题是我们不能使用组函数，但是这个查询可以是一个备用的方法。

SELECT t_pk.id
     , t1.count AS a_col_count
     , t1.col_clob AS a_col_clob
     , t2.count AS b_col_count
     , t2.col_clob AS b_col_clob
  FROM (SELECT DISTINCT id FROM table_1) t_pk
  LEFT JOIN table_1 t1 ON t_pk.id = t1.id AND t1.col1 = 'a'
  LEFT JOIN table_1 t2 ON t_pk.id = t2.id AND t1.col1 = 'b';

或者更复杂的方式，如果你需要从更复杂的子查询中提取数据，而不仅仅是一个表（但在下面的例子中，我仍然只使用一个表来简化例子）。或者，除了“a”和“B”，我们可以找到字母表中的所有其他字母，但我们只需要“a”和“B”。同时，该列不是索引的一部分。然后，重用所选数据可能是有意义的。

WITH t as (
  SELECT t1.id
       , CASE WHEN t1.col1 = 'a' THEN count END AS a_col_count
       , CASE WHEN t1.col1 = 'a' THEN col_clob END AS a_col_clob
       , CASE WHEN t1.col1 = 'b' THEN count END AS b_col_count
       , CASE WHEN t1.col1 = 'b' THEN col_clob END AS b_col_clob
    FROM table_1 t1
   WHERE t1.col1 IN ('a', 'b')
)
SELECT t_pk.id,
       t1.a_col_count,
       t1.a_col_clob,
       t2.a_col_count,
       t2.a_col_clob,
  FROM (SELECT DISTINCT id FROM t) t_pk
  LEFT JOIN t t1 ON t_pk.id = t1.id
    AND (t1.count is not null OR t1.col_clob is not null)
  LEFT JOIN t t2 ON t_pk.id = t2.id
    AND (t2.count is not null OR t2.col_clob is not null);