Tom, dxl,
Regarding dxl's question at bookmark
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4273679444401#19727549535105 The query below almost does the work but with a little sacrifice that some output rows have a few more characters than requested.
Insert into T1 (C1,C2) values ('aa','abcd');
Insert into T1 (C1,C2) values ('aa','efgh');
Insert into T1 (C1,C2) values ('aa','ijkl');
Insert into T1 (C1,C2) values ('aa','mnop');
Insert into T1 (C1,C2) values ('aa','qrst');
Insert into T1 (C1,C2) values ('aa','uvwx');
Insert into T1 (C1,C2) values ('aa','yz');
Insert into T1 (C1,C2) values ('bb','1234');
Insert into T1 (C1,C2) values ('bb','5678');
Insert into T1 (C1,C2) values ('bb','910');
Insert into T1 (C1,C2) values ('bb','1112');
Insert into T1 (C1,C2) values ('bb','1314');
Insert into T1 (C1,C2) values ('aa','abcd');
Insert into T1 (C1,C2) values ('aa','efgh');
Insert into T1 (C1,C2) values ('aa','ijkl');
Insert into T1 (C1,C2) values ('aa','mnop');
Insert into T1 (C1,C2) values ('aa','qrst');
Insert into T1 (C1,C2) values ('aa','uvwx');
Insert into T1 (C1,C2) values ('aa','yz');
Insert into T1 (C1,C2) values ('bb','1234');
Insert into T1 (C1,C2) values ('bb','5678');
Insert into T1 (C1,C2) values ('bb','910');
Insert into T1 (C1,C2) values ('bb','1112');
Insert into T1 (C1,C2) values ('bb','1314');
Insert into T1 (C1,C2) values ('aa','abcd');
Insert into T1 (C1,C2) values ('aa','efgh');
Insert into T1 (C1,C2) values ('aa','ijkl');
Insert into T1 (C1,C2) values ('aa','mnop');
Insert into T1 (C1,C2) values ('aa','qrst');
Insert into T1 (C1,C2) values ('aa','uvwx');
Insert into T1 (C1,C2) values ('aa','yz');
Insert into T1 (C1,C2) values ('bb','1234');
Insert into T1 (C1,C2) values ('bb','5678');
Insert into T1 (C1,C2) values ('bb','910');
Insert into T1 (C1,C2) values ('bb','1112');
Insert into T1 (C1,C2) values ('bb','1314');
Insert into T1 (C1,C2) values ('aa','abcd');
Insert into T1 (C1,C2) values ('aa','efgh');
Insert into T1 (C1,C2) values ('aa','ijkl');
Insert into T1 (C1,C2) values ('aa','mnop');
Insert into T1 (C1,C2) values ('aa','qrst');
Insert into T1 (C1,C2) values ('aa','uvwx');
Insert into T1 (C1,C2) values ('aa','yz');
Insert into T1 (C1,C2) values ('bb','1234');
Insert into T1 (C1,C2) values ('bb','5678');
Insert into T1 (C1,C2) values ('bb','910');
Insert into T1 (C1,C2) values ('bb','1112');
Insert into T1 (C1,C2) values ('bb','1314');
Insert into T1 (C1,C2) values ('aa','abcd');
Insert into T1 (C1,C2) values ('aa','efgh');
Insert into T1 (C1,C2) values ('aa','ijkl');
Insert into T1 (C1,C2) values ('aa','mnop');
Insert into T1 (C1,C2) values ('aa','qrst');
Insert into T1 (C1,C2) values ('aa','uvwx');
Insert into T1 (C1,C2) values ('aa','yz');
Insert into T1 (C1,C2) values ('bb','1234');
Insert into T1 (C1,C2) values ('bb','5678');
Insert into T1 (C1,C2) values ('bb','910');
Insert into T1 (C1,C2) values ('bb','1112');
Insert into T1 (C1,C2) values ('bb','1314');
Insert into T1 (C1,C2) values ('aa','abcd');
Insert into T1 (C1,C2) values ('aa','efgh');
Insert into T1 (C1,C2) values ('aa','ijkl');
Insert into T1 (C1,C2) values ('aa','mnop');
Insert into T1 (C1,C2) values ('aa','qrst');
Insert into T1 (C1,C2) values ('aa','uvwx');
Insert into T1 (C1,C2) values ('aa','yz');
Insert into T1 (C1,C2) values ('bb','1234');
Insert into T1 (C1,C2) values ('bb','5678');
Insert into T1 (C1,C2) values ('bb','910');
Insert into T1 (C1,C2) values ('bb','1112');
Insert into T1 (C1,C2) values ('bb','1314');
SELECT c1,
ltrim(MAX(catstr), '~') AS output,
length(ltrim(MAX(catstr), '~')) AS output_len
FROM (SELECT c1,
group_id,
sys_connect_by_path(c2, '~') AS catstr
FROM (SELECT c1,
c2,
row_id,
lag(row_id) over(PARTITION BY c1, group_id ORDER BY row_id) AS prev_rid,
group_id
FROM (SELECT c1,
c2,
floor((cum_len - 1) / 20) AS group_id,
row_id
FROM (SELECT c1,
c2,
ROWID AS row_id,
SUM(length(c2) + 1) over(PARTITION BY c1 ORDER BY ROWID rows BETWEEN unbounded preceding AND CURRENT ROW) AS cum_len
FROM t1)))
START WITH prev_rid IS NULL
CONNECT BY PRIOR row_id = prev_rid)
GROUP BY c1,
group_id
ORDER BY c1,
group_id;
C1 OUTPUT OUTPUT_LEN
----- ------------------------- ----------
aa abcd~efgh~ijkl~mnop 19
aa qrst~uvwx~yz~abcd 17
aa efgh~ijkl~mnop~qrst 19
aa uvwx~yz~abcd~efgh 17
aa ijkl~mnop~qrst~uvwx~yz 22
aa abcd~efgh~ijkl~mnop 19
aa qrst~uvwx~yz~abcd 17
aa efgh~ijkl~mnop~qrst 19
aa uvwx~yz~abcd~efgh~ijkl 22
aa mnop~qrst~uvwx~yz 17
bb 1234~5678~910~1112 18
bb 1314~1234~5678~910 18
bb 1112~1314~1234~5678 19
bb 910~1112~1314~1234 18
bb 5678~910~1112~1314 18
bb 1234~5678~910~1112~1314 23
bb 1234~5678~910~1112 18
bb 1314 4
18 rows selected.