Oracle SQL-通过串联行创建字符串，但不超过X个字符 [英] Oracle SQL - create string by concatenating rows but does not exceed X number of characters

问题描述

首先，我尝试将行连接成用逗号分隔的字符串.其次，我不希望字符串超过X个字符数.如果要超过，则从下一行开始创建另一个字符串，而不用从每行切开字符.我正在尝试通过oracle SQL实现这一点.

First I am trying to concatenate rows into string separated by comma. Second I do not want the string to be exceeded X number of character. If going to exceed then create another string from next row onward without slitting the character from each rows. I am trying to achieve this through oracle SQL.

我正在查看LISTAGG，它为我提供了连接字符串，但是我不知道如何在不从每一行中拆分值的情况下按X个字符进行拆分，比如说我的X = 15个字符.我使用下面的代码得到的是

I am looking at LISTAGG which give me the concatenated string but I have not idea how I can split by X number of characters without splitting a values from each rows, let's say my X = 15 characters. What I get using the below code is

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

select listagg(x, ',') within group(order by x)
from (select level x from dual
      connect by level <= 20
      )
;

我的预期输出将是

第1行:1、2、3、4、5、6、7、8(15个字符)

row 1: 1,2,3,4,5,6,7,8 (15 characters)

第2行:9、10、11、12、13(13个字符，因为如果我从下一行中添加值，它将超过15个字符)

row 2: 9,10,11,12,13 (13 characters because that going to exceed 15 if I add value from next row)

第3行:14、15、16、17、18(14个字符)

row 3: 14,15,16,17,18 (14 characters)

第4行:19,20(5个字符)

row 4: 19,20 (5 characters)

我仍然不确定是否可以实现这一目标，任何建议或提示将不胜感激.

I am still not sure this can be achieved, any suggestion or hint will be most appreciated.

推荐答案

我尝试了以下查询，并获得了预期的结果.

I tried the following query and achieved the desired result.

SELECT
    LISTAGG(X, ',') WITHIN GROUP(
        ORDER BY
            X
    ) as result
FROM
    (
        SELECT
            X,
            SUM(LENGTH(X)) OVER(
            ORDER BY
                X
            ) LENGTH_X,
            COUNT(1) OVER(
            ORDER BY
                X
            ) - 1 AS COMMAS
        FROM
            (
                SELECT
                    LEVEL   X
                FROM
                    DUAL
                CONNECT BY
                    LEVEL <= 20
            )
    )
GROUP BY
    CEIL((LENGTH_X + COMMAS) / 15);

输出:

db<>小提琴演示

干杯！

这篇关于Oracle SQL-通过串联行创建字符串，但不超过X个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！