我想将每周有多个值的表更改为每周有一行的所有值。但当我使用listagg()时，空间就用完了。我无法更改最大字符串大小(ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE)，并且我不想截断生成的字符串。然而，我可以继续下一行，但我不确定如何操作。例如，如果我的输入是：
Week SKU
202001 598441
202001 846541
202001 77557
202001 57813
202001 5741651
202001 21684135
202001 5451516
202001 545129
202001 98754123
202001 5644242
202002 68454155
...
假设输入文件足够长，会触发错误：
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.
然后我想要一段类似的代码：
select
weeknr,
''''||listagg(sku, ''',''', ON OVERFLOW NEXT LINE) within group(order by weeknr)||'''' sku_numbers
from
input_table
group by
weeknr
所以我的输出会是这样的：
Week SKU
202001 '598441','846541','77557','57813','5741651','21684135'
202001 '5451516','545129','98754123','5644242'
202002 '68454155',...
...
这可能吗？非常感谢您的帮助！
您可以使用分析函数来查找字符串长度的SUM，然后使用它进行如下分组：
SELECT WEEKNR,
'''' || LISTAGG(SKU, ''',''') WITHIN GROUP(ORDER BY WEEKNR) || '''' SKU_NUMBERS
FROM (
SELECT WEEKNR,
FLOOR(SUM(LENGTH(SKU) + 3)
OVER(PARTITION BY WEEKNR ORDER BY SKU) / 4000) AS GROUP_NUMBER,
SKU
FROM INPUT_TABLE
)
GROUP BY WEEKNR, GROUP_NUMBER
您可以使用MATCH_RECOGNIZE逐行检查字符串是否超过4000个字符，并将小于4000个字符的值分配给组，以便使用LISTAGG:
SELECT week,
''''||LISTAGG( sku, ''',''' ) WITHIN GROUP ( ORDER BY sku )||'''' AS skus
FROM table_name
MATCH_RECOGNIZE (
PARTITION BY Week
ORDER BY SKU
MEASURES
MATCH_NUMBER() AS mno
ALL ROWS PER MATCH
PATTERN ( concatenated_string+ )
DEFINE
concatenated_string AS SUM(LENGTH(SKU)+3)-1 <= 4000
)
GROUP BY week, mno
对于样本数据：
CREATE TABLE table_name ( Week, SKU ) AS
SELECT *
FROM (
SELECT 202001 AS week, 10000000+LEVEL AS sku FROM DUAL CONNECT BY LEVEL <= 500
UNION ALL
SELECT 202002, 10000000+LEVEL FROM DUAL CONNECT BY LEVEL <= 100
)
ORDER BY week, DBMS_RANDOM.VALUE; -- put the rows into a random order in the table.
输出：
WEEK|SKUS-----：|：-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------202001 |"10000001"、"0000002"、"100000003"、"1000000 4"、"00000 5"、"20000006"、"000007"、"800008"、"5000009"、"100000 10"、"10万11"、"0万12"、"100万13"、"1万14"、"11万15"、"12万16"、"14万17"、"16万18"、"19"、"20万20"、"21万21"、"22"、"23"、"24"、"25"、"26"、"27"、"28"、"29"、"0000030'、0000031'、0000032'、0000033'、0000034'、0000035'，"10000036"、"10000037"、"0000038"、"100000 39"、"1000000 40"、"00000 41"、"500000 42"、"00000043"、"40000044"、"1000000045"、"8000046"、"50000047"、"000048"、"9000049"、"12000050"、"105051"、"2000052"、"1500053"、"1400054"、"3000055"、"1600056"、"101057"、"1100058"、"1011059"、，'00000066'，'00000067'，'0000068'，'000000 69'，'00000 70'，'100000 71'，"10000072"、"10000073"、"0000074"、"100000 75"、"1000000 76"、"00000 77"、"10万78"、"100万79"、"0万80"、"1万81"、"1000万82"、"50万83"、"40万84"、"70万85"、"60万86"、"30万87"、"80万88"、"110万89"、"90万90"、"101万91"、"102万92"、"103万93"、"104万94"、"105万95"、"106万96"、，'10000102'，'10000103'，'100000104'，'1000105'，'110000106'，'1000107'，"10000108'、10000109'、10000110'、10000111'、10000112'、10000113'、10000114'、10000115'、10000116'、10000117'、10000118'、10000119'、10000120'、10000121'、10000122'、10000123'、10000124'、10000125'、10000126'、10000127'、10000128'、10000129'、10000130'、10000131'、10000132'、10000133'、10000134'、10000135'、10000136'、10000137'，'10000138'，'10000139'，'100000140'，'1000141'，'10001142'，'110000143'，"10000144'、10000145'、10000146'、10000147'、10000148'、10000149'、10000150'、10000151'、10000152'、10000153'、10000154'、10000155'、10000156'、10000157'、10000158'、10000159'、10000160'、10000161'、10000162'、10000163'、10000164'、10000165'、10000166'、10000167'、10000168'、10000169'、10000170'、10000171'、10000172'、10000173'，'10000174'，'10000175'，'100000176'，'1000177'，'10000 178'，'100000 179'，10000180'、10000181'、10000182'、10000183'、10000184'、10000185'、10000186'、10000187'、10000188'、10000189'、10000190'、10000191'、10000192'、10000193'、10000194'、10000195'、10000196'、10000197'、10000198'、10000199'、10000200'、10000201'、10000202'、10000203'、10000204'、10000205'、10000206'、10000207'、10000208'、10000209'，'10000210'，'10000211'，'100000212'，'1000213'，'10000 214'，'100000 215'，10000216'、10000217'、10000218'、10000219'、10000220'、10000221'、10000222'、10000223'、10000224'、10000225'、10000226'、10000227'、10000228'、10000229'、10000230'、10000231'、10000232'、10000233'、10000234'、10000235'、10000236'、10000237'、10000238'、10000239'、10000240'、10000241'、10000242'、10000243'、10000244'、10000245'，'10000246'，'10000247'，'100000248'，'1000249'，'10000 250'，'100000 251'，10000252'、10000253'、10000254'、10000255'、10000256'、10000257'、10000258'、10000259'、10000260'、10000261'、10000262'、10000263'、10000264'、10000265'、10000266'、10000267'、10000268'、10000269'、10000270'、10000271'、10000272'、10000273'、10000274'、10000275'、10000276'、10000277'、10000278'、10000279'、10000280'、10000281'，'10000282'，'10000283'，'100000284'，'1000285'，'10000 286'，'100000 287'，"10000288'、10000289'、10000290'、10000291'、10000292'、10000293'、10000294'、10000295'、10000296'、10000297'、10000298'、10000299'、10000300'、10000301'、10000302'、10000303'、10000304'、10000305'、10000306'、10000307'、10000308'、10000309'、10000310'、10000311'、10000312'、10000313'、10000314'、10000315'、10000316'、10000317'，'10000318'，'10000319'，'100000320'，'1000321'，'10001322'，'110000323'，10000324'、10000325'、10000326'、10000327'、10000328'、10000329'、10000330'、10000331'、10000332'、10000333'、10000334'、10000335'、10000336'、10000337'、10000338'、10000339'、10000340'、10000341'、10000342'、10000343'、10000344'、10000345'、10000346'、10000347'、10000348'、10000349'、10000350'、10000351'、10000352'、0000353’、0000354’、0000355’、0000356’、0000357’、0000358’、10000359’，10000360'，10000361'，10000362'，10000363'202001 |"10000364'，10000365'，10000366'，10000367'，10000368'，1000036'，10000370'，10000371'，10000372'，10000373'，10000374'，10000375'，10000376'，10000377'，10000378'，10000379'，10000380'，10000381'，10000382'，10000383'，10000384'，10000385'，10000386'，10000387'，10000388'，10000389'，10000390'，10000391'1'，'10000392'，'10000393'，'100000394'，'1000395'，'10000 396'，'100000 397'，'1000 398'，"10000399"、"10000400"、"0000401"、"10000 402"、"100000 403"、"40000 404"、"0000 405"、"1000000 406"、"50000 407"、"30000 408"、"20000 409"、《10000 410》、《10000 411》、"10000 412"、《100000 413》、《100000 414》、《0000 415》、《0000416》、《10000417》、《1000000 418》、《40000 419》、0000428'，10000429'，10000430'，10000431'，10000432'，10000433'，10000434'，10000435'、10000436'、10000437'、10000438'、10000439'、10000440'、10000441'、10000442'、10000443'、10000444'、10000445'、10000446'、10000447'、10000448'、10000449'、10000450'、10000451'、10000452'、10000453'、10000454'、10000455'、10000456'、10000457'、10000458'、10000459'、10000460'、10000461'，'00000462'，'10000463'，'100000464'，'10000465'，'1000466'，'10000 467'，'100000 468'，'110000 469'，'1000 470'，"10000471"，"0000472"，"0000473"，"，10000497'，10000498'，10000499'，10000500'202002 |"10000001"、"0000002"、"100000003"、"1000000 4"、"00000 5"、"20000006"、"000007"、"800008"、"5000009"、"100000 10"、"10万11"、"0万12"、"100万13"、"1万14"、"11万15"、"12万16"、"14万17"、"16万18"、"19"、"20万20"、"21万21"、"22"、"23"、"24"、"25"、"26"、"27"、"28"、"29"、"0000030'、0000031'、0000032'、0000033'、0000034'、0000035'，"10000036"、"10000037"、"0000038"、"100000 39"、"1000000 40"、"00000 41"、"500000 42"、"00000043"、"40000044"、"1000000045"、"8000046"、"50000047"、"000048"、"9000049"、"12000050"、"105051"、"2000052"、"1500053"、"1400054"、"3000055"、"1600056"、"101057"、"1100058"、"1011059"、，'00000066'，'00000067'，'0000068'，'000000 69'，'00000 70'，'100000 71'，"10000072"、"10000073"、"0000074"、"100000 75"、"1000000 76"、"00000 77"、"10万78"、"100万79"、"0万80"、"1万81"、"1000万82"、"50万83"、"110万84"、"80万85"、"40万86"、"70万87"、"60万88"、"30万89"、"90万90"、"51万91"、"20万92"、"101万93"、"102万94"、"105万95"、"106万96"、"103万97"、"104万98"、"99万99"，10000100'
db<gt；小提琴这里
由于LISTAGG有4000个字符的限制，请切换到XML。我认为你不能"力"；CCD_ 6采取不同的行动。
SQL> SELECT LISTAGG (dname, ', ') WITHIN GROUP (ORDER BY dname) result FROM dept;
RESULT
--------------------------------------------------------------------------------
ACCOUNTING, OPERATIONS, RESEARCH, SALES
SQL>
SQL> SELECT RTRIM (
2 XMLAGG (XMLELEMENT (e, dname || ', ') ORDER BY dname).EXTRACT (
3 '//text()'),
4 ', ') result
5 FROM dept;
RESULT
--------------------------------------------------------------------------------
ACCOUNTING, OPERATIONS, RESEARCH, SALES
SQL>
您可以创建自己的函数：
CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(30000);
CREATE OR REPLACE FUNCTION JoinTable(tab IN VARCHAR_TABLE_TYPE, Joiner IN VARCHAR2) RETURN VARCHAR2 IS
res VARCHAR2(30000); -- or even: res CLOB;
BEGIN
IF tab IS NULL THEN
RETURN NULL;
END IF;
IF tab.COUNT = 0 THEN
RETURN NULL;
END IF;
FOR i IN tab.FIRST..tab.LAST-1 LOOP
res := res || tab(i) || Joiner;
END LOOP;
RETURN res || tab(tab.LAST);
END JoinTable;
然后你可以这样使用它：
SELECT
JoinTable(
CAST(MULTISET(SELECT sku FROM input_table order by weeknr) AS VARCHAR_TABLE_TYPE),
','
) as sku_numbers
from dual