oracle of 2 WMSYS.WM_of SQL SkillsCONCAT function for splicing multiple lines of records together with commas
- 2021-06-28 14:26:17
- OfStack
Requirements:
Now contact BI system, because there are many transaction records in business system, often some supervisors need to see all the records, but do not want to scroll, think of one eye to see all, then think of the form of string splicing.
Solution: Use the function WMSYS.WM_that comes with OracleCONCAT, splice.
Function Limit: Its output cannot exceed 4000 bytes.
To keep SQL from failing and to meet business needs, use'. 'for parts over 4000 bytes.
Implement SQL as follows:
insert into tmp_product
select 'A','ProductA'||rownum from dual
connect by level < 100
union all
select 'B','ProductB'||rownum from dual
connect by level < 300
union all
select 'C','ProductC'||rownum from dual
connect by level < 400
union all
select 'D','ProductD'||rownum from dual
connect by level < 500
union all
select 'E','ProductE'||rownum from dual
connect by level < 600;
Now contact BI system, because there are many transaction records in business system, often some supervisors need to see all the records, but do not want to scroll, think of one eye to see all, then think of the form of string splicing.
Solution: Use the function WMSYS.WM_that comes with OracleCONCAT, splice.
Function Limit: Its output cannot exceed 4000 bytes.
To keep SQL from failing and to meet business needs, use'. 'for parts over 4000 bytes.
Implement SQL as follows:
CREATE TABLE TMP_PRODUCT
(PRODUCT_TYPE VARCHAR2(255),
PRODUCT_NAME VARCHAR2(255));
insert into tmp_product
select 'A','ProductA'||rownum from dual
connect by level < 100
union all
select 'B','ProductB'||rownum from dual
connect by level < 300
union all
select 'C','ProductC'||rownum from dual
connect by level < 400
union all
select 'D','ProductD'||rownum from dual
connect by level < 500
union all
select 'E','ProductE'||rownum from dual
connect by level < 600;
SELECT PRODUCT_TYPE,
WM_CONCAT(PRODUCT_NAME) || MAX(STR) AS PRODUCT_MULTI_NAME
FROM (SELECT PRODUCT_TYPE,
PRODUCT_NAME,
CASE
WHEN ALL_SUM > 4000 THEN
'...'
ELSE
NULL
END AS STR
FROM (SELECT PRODUCT_TYPE,
PRODUCT_NAME,
SUM(VSIZE(PRODUCT_NAME || ',')) OVER(PARTITION BY PRODUCT_TYPE) AS ALL_SUM,
SUM(VSIZE(PRODUCT_NAME || ',')) OVER(PARTITION BY PRODUCT_TYPE ORDER BY PRODUCT_NAME) AS UP_SUM
FROM TMP_PRODUCT)
WHERE (UP_SUM <= 3998 AND ALL_SUM > 4000)
OR ALL_SUM <= 4001)
GROUP BY PRODUCT_TYPE