时间戳

首页 > 解决方案 > 将多行串联成单列，最大行数限制为 1000

sql - 将多行串联成单列，最大行数限制为 1000

问题描述

我有这个查询，我使用这个函数连接单列中的行。

LISTAGG(ATTRIBUTE_CHAR9, ' ; ') WITHIN GROUP (ORDER BY ATTRIBUTE_CHAR5)

但是连接结果太长，无法在单列或 excel 中处理，因为它超过了 32676 位长度，这也是 excel 支持的最大长度。

有什么方法可以将列分成多行，如 5 行或 10 行。假设我将 5000 行数据连接到一个不起作用的列中，所以我希望将它除以 1000 行，这样我将得到 5 行而不是 1 行。

这是查询

WITH CTE AS(
SELECT 
ORG_NAME AS NAME, 
(SELECT DESCRIPTION FROM NODETABLE WHERE ID = O.ORG_SUPPLIER_TYPE) SUPPLIER_TYPE, 
NVL(LIFE.DESCRIPTION, 'Preliminary') AS LIFECYCLE_PHASE, O.ORG_NUMBER AS "NUMBER", O.ORG_SUPPLIER_ID DUNS,
CONCAT(CONCAT(CONCAT(CONCAT(O.ORG_NAME, ' '), '('), O.ORG_NUMBER),')') DISPLAY_NAME,
O.ORG_SHORT_NAME DESCRIPTION, 
(
SELECT STL.ENTRYVALUE 
FROM LISTENTRY STL
INNER JOIN LISTNAME LSTN ON LSTN.ID = STL.PARENTID AND LSTN.NAME = 'Yes/No Selection List'
WHERE STL.ENTRYID = O.ORG_WEB_FLAG
) WEB_SUPPLIER,
(
SELECT STL.ENTRYVALUE 
FROM LISTENTRY STL
INNER JOIN LISTNAME LSTN ON LSTN.ID = STL.PARENTID AND LSTN.NAME = 'Currency List'
WHERE STL.ENTRYID = O.ORG_CURRENCY_CODE
) CORPORATE_CURRENCY,
O.ORG_ADDRESS ADDRESS,
(
SELECT STL.ENTRYVALUE 
FROM LISTENTRY STL
INNER JOIN LISTNAME LSTN ON LSTN.ID = STL.PARENTID AND LSTN.NAME = 'Region/Province/State'
WHERE STL.ENTRYID = O.GEOGRAPHY
) GEOGRAPHY,
O.GEOGRAPHY ASASDFASD,
O.ORG_CITY CITY, 
O.ORG_POSTAL_CODE "POSTAL/ZIP_CODE",
O.ORG_PHONE PHONE,
O.ORG_FAX FAX,
O.ORG_URL URL,
O.ORG_MAX_USERS MAX_NUMBER_OF_CONTACT_USERS,
O.ORG_MAX_SUPER_SUPPLIER_USERS MAX_LICENSED_CONTACT_USERS,
O.ORG_POWER_LICENSES MAX_NAMED_CONTACT_USERS,
(
SELECT STL.ENTRYVALUE 
FROM LISTENTRY STL
INNER JOIN LISTNAME LSTN ON LSTN.ID = STL.PARENTID AND LSTN.NAME = 'Users'
WHERE STL.ENTRYID = O.ORG_DEFAULT_RFQ_RECEIVER_FK
) DEFAULT_RFQ_RECIPIENT,
(
SELECT STL.ENTRYVALUE 
FROM LISTENTRY STL
INNER JOIN LISTNAME LSTN ON LSTN.ID = STL.PARENTID AND LSTN.NAME = 'Users'
WHERE STL.ENTRYID = O.ORG_DEFAULT_DECL_RECEIVER_FK
) DEFAULT_DECLARATION_RECIPIENT, 
(
SELECT STL.ENTRYVALUE 
FROM LISTENTRY STL
INNER JOIN LISTNAME LSTN ON LSTN.ID = STL.PARENTID AND LSTN.NAME = 'Company Type'
WHERE STL.ENTRYID = PG2.LIST11
) COMPANY_TYPE,
(SELECT TEXT FROM AGILE_FLEX af WHERE ATTID = 1331 AND af.ID = O.ORG_PK) MANUFACTURER_LEGAL_NAME,
PG2.TEXT02 "SUPPLIER/MFR_ID", 
RTRIM(LTRIM(PG2.MULTILIST02, ','), ',') RESPONSIBLE_SDT, 
RTRIM(LTRIM(PG2.MULTILIST01, ','), ',') RESPONSIBLE_GSM, 
PG2.TEXT02 ORACLE_SUPPLIER_NUMBER,
CONCAT(CONCAT(AU.FIRST_NAME, ' '), AU.LAST_NAME) CONTACT_USER

FROM ORGANIZATION O
LEFT JOIN AGILE.NODETABLE LIFE ON LIFE.ID = O.ORG_STATUS
LEFT JOIN AGILE.PAGE_TWO PG2 ON PG2.ID = O.ORG_PK 
LEFT JOIN AGILEUSER AU ON AU.HOME_ORG = O.ORG_PK 
WHERE O.ORG_PK = 1002
), PGIND AS

(
SELECT DISTINCT 
NAME, SUPPLIER_TYPE, LIFECYCLE_PHASE, "NUMBER", DUNS, DISPLAY_NAME, DESCRIPTION, WEB_SUPPLIER, CORPORATE_CURRENCY, ADDRESS, GEOGRAPHY, CITY, "POSTAL/ZIP_CODE", PHONE, FAX, URL, MAX_NUMBER_OF_CONTACT_USERS, 
MAX_LICENSED_CONTACT_USERS, MAX_NAMED_CONTACT_USERS, DEFAULT_RFQ_RECIPIENT, DEFAULT_DECLARATION_RECIPIENT, COMPANY_TYPE, MANUFACTURER_LEGAL_NAME, "SUPPLIER/MFR_ID", 
REGEXP_SUBSTR(RESPONSIBLE_SDT, '[^,]+', 1, column_value) RESPONSIBLE_SDT, 
REGEXP_SUBSTR(RESPONSIBLE_GSM, '[^,]+', 1, column_value) RESPONSIBLE_GSM, 
ORACLE_SUPPLIER_NUMBER, CONTACT_USER
FROM CTE

CROSS JOIN TABLE(CAST(MULTISET(SELECT LEVEL FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(NVL(RESPONSIBLE_SDT, RESPONSIBLE_GSM), ',') + 1
) AS sys.odcinumberlist))
), FINALCTE AS(

SELECT DISTINCT NULL AS "Transaction Type", NULL AS "Batch ID", NULL AS "Batch Number", NULL AS "Item Number", NULL AS "Organization Code", NULL AS "Source System Code", NULL AS "Source System Reference", 'Supplier_Supp_Info_ATTGRP' AS "Attribute Group Code", 
NULL AS ATTRIBUTE_CHAR1, 
SUPPLIER_TYPE AS ATTRIBUTE_CHAR2, 
LIFECYCLE_PHASE AS ATTRIBUTE_CHAR3, 
"NUMBER" ATTRIBUTE_CHAR4, 
DISPLAY_NAME AS ATTRIBUTE_CHAR5, 
NULL AS ATTRIBUTE_CHAR6, NULL AS ATTRIBUTE_CHAR7, NULL AS ATTRIBUTE_CHAR8, 
CONTACT_USER AS ATTRIBUTE_CHAR9,
MANUFACTURER_LEGAL_NAME AS ATTRIBUTE_CHAR10, 
"SUPPLIER/MFR_ID" AS ATTRIBUTE_CHAR11, 
ORACLE_SUPPLIER_NUMBER AS ATTRIBUTE_CHAR12, 
NULL AS ATTRIBUTE_CHAR13, NULL AS ATTRIBUTE_CHAR14, NULL AS ATTRIBUTE_CHAR15,  NULL AS ATTRIBUTE_CHAR16, NULL AS ATTRIBUTE_CHAR17, NULL AS ATTRIBUTE_CHAR18, NULL AS ATTRIBUTE_CHAR19,
NULL AS ATTRIBUTE_CHAR20, NULL AS ATTRIBUTE_NUMBER1, NULL AS ATTRIBUTE_NUMBER2, NULL AS ATTRIBUTE_NUMBER3, NULL AS ATTRIBUTE_NUMBER4, NULL AS ATTRIBUTE_NUMBER5, NULL AS ATTRIBUTE_NUMBER6, NULL AS ATTRIBUTE_NUMBER7, NULL AS ATTRIBUTE_NUMBER8,
NULL AS ATTRIBUTE_NUMBER9, NULL AS ATTRIBUTE_NUMBER10, NULL AS ATTRIBUTE_DATE1, NULL AS ATTRIBUTE_DATE2, NULL AS ATTRIBUTE_DATE3, NULL AS ATTRIBUTE_DATE4, NULL AS ATTRIBUTE_DATE5, NULL AS ATTRIBUTE_CHAR21, NULL AS ATTRIBUTE_CHAR22, NULL AS ATTRIBUTE_CHAR23,
NULL AS ATTRIBUTE_CHAR24, NULL AS ATTRIBUTE_CHAR25, NULL AS ATTRIBUTE_CHAR26, NULL AS ATTRIBUTE_CHAR27, NULL AS ATTRIBUTE_CHAR28, NULL AS ATTRIBUTE_CHAR29, NULL AS ATTRIBUTE_CHAR30, NULL AS ATTRIBUTE_CHAR31, NULL AS ATTRIBUTE_CHAR32, NULL AS ATTRIBUTE_CHAR33, 
NULL AS ATTRIBUTE_CHAR34, NULL AS ATTRIBUTE_CHAR35, NULL AS ATTRIBUTE_CHAR36, NULL AS ATTRIBUTE_CHAR37, NULL AS ATTRIBUTE_CHAR38, NULL AS ATTRIBUTE_CHAR39, NULL AS ATTRIBUTE_CHAR40, NULL AS ATTRIBUTE_NUMBER11, NULL AS ATTRIBUTE_NUMBER12, NULL AS ATTRIBUTE_NUMBER13, NULL AS ATTRIBUTE_NUMBER14,  
NULL AS ATTRIBUTE_NUMBER15, NULL AS ATTRIBUTE_NUMBER16, NULL AS ATTRIBUTE_NUMBER17, NULL AS ATTRIBUTE_NUMBER18, NULL AS ATTRIBUTE_NUMBER19, NULL AS ATTRIBUTE_NUMBER20, NULL AS ATTRIBUTE_DATE6, NULL AS ATTRIBUTE_DATE7, NULL AS ATTRIBUTE_DATE8, NULL AS ATTRIBUTE_DATE9, NULL AS ATTRIBUTE_DATE10,
NULL AS ATTRIBUTE_TIMESTAMP1, NULL AS ATTRIBUTE_TIMESTAMP2, NULL AS ATTRIBUTE_TIMESTAMP3, NULL AS ATTRIBUTE_TIMESTAMP4, NULL AS ATTRIBUTE_TIMESTAMP5, NULL AS ATTRIBUTE_TIMESTAMP6, NULL AS ATTRIBUTE_TIMESTAMP7, NULL AS ATTRIBUTE_TIMESTAMP8, NULL AS ATTRIBUTE_TIMESTAMP9, NULL AS ATTRIBUTE_TIMESTAMP10,
NULL AS VERSION_START_DATE, NULL AS VERSION_REVISION_CODE, NULL AS ATTRIBUTE_NUMBER1_UOM_NAME, NULL AS ATTRIBUTE_NUMBER2_UOM_NAME, NULL AS ATTRIBUTE_NUMBER3_UOM_NAME, NULL AS ATTRIBUTE_NUMBER4_UOM_NAME, NULL AS ATTRIBUTE_NUMBER5_UOM_NAME, NULL AS ATTRIBUTE_NUMBER6_UOM_NAME,
NULL AS ATTRIBUTE_NUMBER7_UOM_NAME, NULL AS ATTRIBUTE_NUMBER8_UOM_NAME, NULL AS ATTRIBUTE_NUMBER9_UOM_NAME, NULL AS ATTRIBUTE_NUMBER10_UOM_NAME, NULL AS ATTRIBUTE_NUMBER11_UOM_NAME, NULL AS ATTRIBUTE_NUMBER12_UOM_NAME, NULL AS ATTRIBUTE_NUMBER13_UOM_NAME, NULL AS ATTRIBUTE_NUMBER14_UOM_NAME,
NULL AS ATTRIBUTE_NUMBER15_UOM_NAME, NULL AS ATTRIBUTE_NUMBER16_UOM_NAME, NULL AS ATTRIBUTE_NUMBER17_UOM_NAME, NULL AS ATTRIBUTE_NUMBER18_UOM_NAME, NULL AS ATTRIBUTE_NUMBER19_UOM_NAME, NULL AS ATTRIBUTE_NUMBER20_UOM_NAME, NULL AS ATTRIBUTE_NUMBER1_UE, NULL AS ATTRIBUTE_NUMBER2_UE,
NULL AS ATTRIBUTE_NUMBER3_UE, NULL AS ATTRIBUTE_NUMBER4_UE, NULL AS ATTRIBUTE_NUMBER5_UE, NULL AS ATTRIBUTE_NUMBER6_UE, NULL AS ATTRIBUTE_NUMBER7_UE, NULL AS ATTRIBUTE_NUMBER8_UE, NULL AS ATTRIBUTE_NUMBER9_UE, NULL AS ATTRIBUTE_NUMBER10_UE, NULL AS ATTRIBUTE_NUMBER11_UE, NULL AS ATTRIBUTE_NUMBER12_UE,
NULL AS ATTRIBUTE_NUMBER13_UE, NULL AS ATTRIBUTE_NUMBER14_UE, NULL AS ATTRIBUTE_NUMBER15_UE, NULL AS ATTRIBUTE_NUMBER16_UE, NULL AS ATTRIBUTE_NUMBER17_UE, NULL AS ATTRIBUTE_NUMBER18_UE, NULL AS ATTRIBUTE_NUMBER19_UE, NULL AS ATTRIBUTE_NUMBER20_UE


FROM PGIND
WHERE LIFECYCLE_PHASE <> 'Obsolete' 
)

SELECT 
"Transaction Type", "Batch ID", "Batch Number", "Item Number", "Organization Code", "Source System Code", "Source System Reference", "Attribute Group Code", 
ATTRIBUTE_CHAR1, ATTRIBUTE_CHAR2, ATTRIBUTE_CHAR3, ATTRIBUTE_CHAR4, ATTRIBUTE_CHAR5, ATTRIBUTE_CHAR6, ATTRIBUTE_CHAR7, ATTRIBUTE_CHAR8, 
LISTAGG(ATTRIBUTE_CHAR9, ' ; ') WITHIN GROUP (ORDER BY ATTRIBUTE_CHAR5) ATTRIBUTE_CHAR9,
--RTRIM(XMLAGG(XMLELEMENT(e,ATTRIBUTE_CHAR9,'; ').EXTRACT('//text()')).GetClobVal(),',') ATTRIBUTE_CHAR9, 
ATTRIBUTE_CHAR10, 
ATTRIBUTE_CHAR11, ATTRIBUTE_CHAR12, ATTRIBUTE_CHAR13, ATTRIBUTE_CHAR14, ATTRIBUTE_CHAR15,  ATTRIBUTE_CHAR16, ATTRIBUTE_CHAR17, ATTRIBUTE_CHAR18, ATTRIBUTE_CHAR19, ATTRIBUTE_CHAR20, 
ATTRIBUTE_NUMBER1, ATTRIBUTE_NUMBER2, ATTRIBUTE_NUMBER3, ATTRIBUTE_NUMBER4, ATTRIBUTE_NUMBER5, ATTRIBUTE_NUMBER6, ATTRIBUTE_NUMBER7, ATTRIBUTE_NUMBER8, ATTRIBUTE_NUMBER9, ATTRIBUTE_NUMBER10, 
ATTRIBUTE_DATE1, ATTRIBUTE_DATE2, ATTRIBUTE_DATE3, ATTRIBUTE_DATE4, ATTRIBUTE_DATE5, 
ATTRIBUTE_CHAR21, ATTRIBUTE_CHAR22, ATTRIBUTE_CHAR23, ATTRIBUTE_CHAR24, ATTRIBUTE_CHAR25, ATTRIBUTE_CHAR26, ATTRIBUTE_CHAR27, ATTRIBUTE_CHAR28, ATTRIBUTE_CHAR29, ATTRIBUTE_CHAR30, 
ATTRIBUTE_CHAR31, ATTRIBUTE_CHAR32, ATTRIBUTE_CHAR33, ATTRIBUTE_CHAR34, ATTRIBUTE_CHAR35, ATTRIBUTE_CHAR36, ATTRIBUTE_CHAR37, ATTRIBUTE_CHAR38, ATTRIBUTE_CHAR39, ATTRIBUTE_CHAR40, 
ATTRIBUTE_NUMBER11, ATTRIBUTE_NUMBER12, ATTRIBUTE_NUMBER13, ATTRIBUTE_NUMBER14, ATTRIBUTE_NUMBER15, ATTRIBUTE_NUMBER16, ATTRIBUTE_NUMBER17, ATTRIBUTE_NUMBER18, ATTRIBUTE_NUMBER19, ATTRIBUTE_NUMBER20, 
ATTRIBUTE_DATE6, ATTRIBUTE_DATE7, ATTRIBUTE_DATE8, ATTRIBUTE_DATE9, ATTRIBUTE_DATE10, 
ATTRIBUTE_TIMESTAMP1, ATTRIBUTE_TIMESTAMP2, ATTRIBUTE_TIMESTAMP3, ATTRIBUTE_TIMESTAMP4, ATTRIBUTE_TIMESTAMP5, ATTRIBUTE_TIMESTAMP6, ATTRIBUTE_TIMESTAMP7, ATTRIBUTE_TIMESTAMP8, ATTRIBUTE_TIMESTAMP9, ATTRIBUTE_TIMESTAMP10,
VERSION_START_DATE, VERSION_REVISION_CODE, 
ATTRIBUTE_NUMBER1_UOM_NAME, ATTRIBUTE_NUMBER2_UOM_NAME, ATTRIBUTE_NUMBER3_UOM_NAME, ATTRIBUTE_NUMBER4_UOM_NAME, ATTRIBUTE_NUMBER5_UOM_NAME, ATTRIBUTE_NUMBER6_UOM_NAME, ATTRIBUTE_NUMBER7_UOM_NAME, ATTRIBUTE_NUMBER8_UOM_NAME, ATTRIBUTE_NUMBER9_UOM_NAME, ATTRIBUTE_NUMBER10_UOM_NAME, 
ATTRIBUTE_NUMBER11_UOM_NAME, ATTRIBUTE_NUMBER12_UOM_NAME, ATTRIBUTE_NUMBER13_UOM_NAME, ATTRIBUTE_NUMBER14_UOM_NAME, ATTRIBUTE_NUMBER15_UOM_NAME, ATTRIBUTE_NUMBER16_UOM_NAME, ATTRIBUTE_NUMBER17_UOM_NAME, ATTRIBUTE_NUMBER18_UOM_NAME, ATTRIBUTE_NUMBER19_UOM_NAME, ATTRIBUTE_NUMBER20_UOM_NAME, 
ATTRIBUTE_NUMBER1_UE, ATTRIBUTE_NUMBER2_UE, ATTRIBUTE_NUMBER3_UE, ATTRIBUTE_NUMBER4_UE, ATTRIBUTE_NUMBER5_UE, ATTRIBUTE_NUMBER6_UE, ATTRIBUTE_NUMBER7_UE, ATTRIBUTE_NUMBER8_UE, ATTRIBUTE_NUMBER9_UE, ATTRIBUTE_NUMBER10_UE, 
ATTRIBUTE_NUMBER11_UE, ATTRIBUTE_NUMBER12_UE, ATTRIBUTE_NUMBER13_UE, ATTRIBUTE_NUMBER14_UE, ATTRIBUTE_NUMBER15_UE, ATTRIBUTE_NUMBER16_UE, ATTRIBUTE_NUMBER17_UE, ATTRIBUTE_NUMBER18_UE, ATTRIBUTE_NUMBER19_UE, ATTRIBUTE_NUMBER20_UE

FROM FINALCTE
GROUP BY 
"Transaction Type", "Batch ID", "Batch Number", "Item Number", "Organization Code", "Source System Code", "Source System Reference", "Attribute Group Code", 
ATTRIBUTE_CHAR1, ATTRIBUTE_CHAR2, ATTRIBUTE_CHAR3, ATTRIBUTE_CHAR4, ATTRIBUTE_CHAR5, ATTRIBUTE_CHAR6, ATTRIBUTE_CHAR7, ATTRIBUTE_CHAR8, ATTRIBUTE_CHAR10, 
ATTRIBUTE_CHAR11, ATTRIBUTE_CHAR12, ATTRIBUTE_CHAR13, ATTRIBUTE_CHAR14, ATTRIBUTE_CHAR15,  ATTRIBUTE_CHAR16, ATTRIBUTE_CHAR17, ATTRIBUTE_CHAR18, ATTRIBUTE_CHAR19, ATTRIBUTE_CHAR20, 
ATTRIBUTE_NUMBER1, ATTRIBUTE_NUMBER2, ATTRIBUTE_NUMBER3, ATTRIBUTE_NUMBER4, ATTRIBUTE_NUMBER5, ATTRIBUTE_NUMBER6, ATTRIBUTE_NUMBER7, ATTRIBUTE_NUMBER8, ATTRIBUTE_NUMBER9, ATTRIBUTE_NUMBER10, 
ATTRIBUTE_DATE1, ATTRIBUTE_DATE2, ATTRIBUTE_DATE3, ATTRIBUTE_DATE4, ATTRIBUTE_DATE5, 
ATTRIBUTE_CHAR21, ATTRIBUTE_CHAR22, ATTRIBUTE_CHAR23, ATTRIBUTE_CHAR24, ATTRIBUTE_CHAR25, ATTRIBUTE_CHAR26, ATTRIBUTE_CHAR27, ATTRIBUTE_CHAR28, ATTRIBUTE_CHAR29, ATTRIBUTE_CHAR30, 
ATTRIBUTE_CHAR31, ATTRIBUTE_CHAR32, ATTRIBUTE_CHAR33, ATTRIBUTE_CHAR34, ATTRIBUTE_CHAR35, ATTRIBUTE_CHAR36, ATTRIBUTE_CHAR37, ATTRIBUTE_CHAR38, ATTRIBUTE_CHAR39, ATTRIBUTE_CHAR40, 
ATTRIBUTE_NUMBER11, ATTRIBUTE_NUMBER12, ATTRIBUTE_NUMBER13, ATTRIBUTE_NUMBER14, ATTRIBUTE_NUMBER15, ATTRIBUTE_NUMBER16, ATTRIBUTE_NUMBER17, ATTRIBUTE_NUMBER18, ATTRIBUTE_NUMBER19, ATTRIBUTE_NUMBER20, 
ATTRIBUTE_DATE6, ATTRIBUTE_DATE7, ATTRIBUTE_DATE8, ATTRIBUTE_DATE9, ATTRIBUTE_DATE10, 
ATTRIBUTE_TIMESTAMP1, ATTRIBUTE_TIMESTAMP2, ATTRIBUTE_TIMESTAMP3, ATTRIBUTE_TIMESTAMP4, ATTRIBUTE_TIMESTAMP5, ATTRIBUTE_TIMESTAMP6, ATTRIBUTE_TIMESTAMP7, ATTRIBUTE_TIMESTAMP8, ATTRIBUTE_TIMESTAMP9, ATTRIBUTE_TIMESTAMP10,
VERSION_START_DATE, VERSION_REVISION_CODE, 
ATTRIBUTE_NUMBER1_UOM_NAME, ATTRIBUTE_NUMBER2_UOM_NAME, ATTRIBUTE_NUMBER3_UOM_NAME, ATTRIBUTE_NUMBER4_UOM_NAME, ATTRIBUTE_NUMBER5_UOM_NAME, ATTRIBUTE_NUMBER6_UOM_NAME, ATTRIBUTE_NUMBER7_UOM_NAME, ATTRIBUTE_NUMBER8_UOM_NAME, ATTRIBUTE_NUMBER9_UOM_NAME, ATTRIBUTE_NUMBER10_UOM_NAME, 
ATTRIBUTE_NUMBER11_UOM_NAME, ATTRIBUTE_NUMBER12_UOM_NAME, ATTRIBUTE_NUMBER13_UOM_NAME, ATTRIBUTE_NUMBER14_UOM_NAME, ATTRIBUTE_NUMBER15_UOM_NAME, ATTRIBUTE_NUMBER16_UOM_NAME, ATTRIBUTE_NUMBER17_UOM_NAME, ATTRIBUTE_NUMBER18_UOM_NAME, ATTRIBUTE_NUMBER19_UOM_NAME, ATTRIBUTE_NUMBER20_UOM_NAME, 
ATTRIBUTE_NUMBER1_UE, ATTRIBUTE_NUMBER2_UE, ATTRIBUTE_NUMBER3_UE, ATTRIBUTE_NUMBER4_UE, ATTRIBUTE_NUMBER5_UE, ATTRIBUTE_NUMBER6_UE, ATTRIBUTE_NUMBER7_UE, ATTRIBUTE_NUMBER8_UE, ATTRIBUTE_NUMBER9_UE, ATTRIBUTE_NUMBER10_UE, 
ATTRIBUTE_NUMBER11_UE, ATTRIBUTE_NUMBER12_UE, ATTRIBUTE_NUMBER13_UE, ATTRIBUTE_NUMBER14_UE, ATTRIBUTE_NUMBER15_UE, ATTRIBUTE_NUMBER16_UE, ATTRIBUTE_NUMBER17_UE, ATTRIBUTE_NUMBER18_UE, ATTRIBUTE_NUMBER19_UE, ATTRIBUTE_NUMBER20_UE
ORDER BY ATTRIBUTE_CHAR5

标签： sqloracle

解决方案

您可以为每 1000 行添加一个附加列作为相同值的表创建一个视图，并使用以下

LISTAGG(ATTRIBUTE_CHAR9, ' ; ') WITHIN GROUP (ORDER BY ATTRIBUTE_CHAR5) 
OVER (PARTITION BY VW_COL_EVERY_1000_rows)

下面相同的生锈实现

            WITH maxrows
         AS (SELECT Max(ROWNUM) rw
             FROM   your_table),
         lvltab
         AS (SELECT 0 lvl
             FROM   dual
             UNION
             SELECT LEVEL lvl
             FROM   dual,
                    maxrows
             CONNECT BY ( 1000 * LEVEL ) <= rw)
    SELECT DISTINCT Listagg(attribute_char9, ' ; ')
                      within GROUP (ORDER BY attribute_char5) over (
                        PARTITION BY lvl1)
    FROM   (SELECT *
            FROM   (SELECT attribute_char9,
                           attribute_char5,
                           CASE
                             WHEN rn >= ( lvl * 1000 ) + 1
                                  AND rn <= ( 1000 * lvl ) + 1000 THEN lvl
                             ELSE NULL
                           END AS lvl1
                    FROM   (SELECT a.*,
                                   ROWNUM rn
                            FROM   your_table a),
                           lvltab)
            WHERE  lvl1 IS NOT NULL);

推荐阅读