在一个列中添加逗号分隔的多个值 [英] Adding multiple values in one column comma separated

问题描述

如果我有一个CLOB字段包含用逗号分隔的多个值，需要总计它们以获得最终输出，我如何在SQL Developer中实现？

示例表：

  STOCK | COST 
 
 ABCDE | 258.40,299.50 
 FGHIJ | 100.50,70.50,95.30 
  

我希望能够为每一行选择总计。 p>

对于希望选择总共557.90的ABCDE

对于希望选择总共266.30的FGHIJ

解决方案

下面是使用CTE（公用表表达式）与处理NULL列表元素（或在查询中明确忽略它们）的正则表达式忽略它们）：

  SQL> - 首先构建基表。 
 SQL> tbl（stk，cst）as（
 select'ABCDE'，'，258.40,299.50'from dual union 
 select'FGHIJ'，'100.50,70.50 ,,, 95.30'from dual 
），
  - 使用逗号作为分隔符将列表转换为一个表。想想它
  - 像内存中的临时表。此正则表达式格式处理NULL列表元素。 
 example_tbl（stock，cost）as（
 select stk，regexp_substr（cst，'（。*？）（，| $）'，1，level，NULL，1）
 from tbl 
 connect by regexp_substr（cst，'（。*？）（，| $）'，1，level）不为null 
 group by stk，level，cst 
）
 -  select * from example_tbl; 
 SELECT stock，to_char（sum（cost），'9990.99'）Total 
 from example_tbl 
 group by stock; 
 
 STOCK TOTAL 
 ----- -------- 
 ABCDE 557.90 
 FGHIJ 266.30 
 
 SQL> 
  

If I have a CLOB field that contains multiple values separated by commas, and need to total them to get a final output, how can I achieve that in SQL Developer?

Example table:

STOCK | COST

ABCDE | 258.40,299.50
FGHIJ | 100.50,70.50,95.30

I would like to be able to select the total for each row.

For ABCDE looking to select a total of 557.90

For FGHIJ looking to select a total of 266.30

解决方案

Here's a way using a CTE (Common Table Expression) with a regex that handles NULL list elements (or explicitly ignore them in the query, SUM ignores them at any rate):

SQL> -- First build the base table.
SQL> with tbl(stk, cst) as (
     select 'ABCDE', ',258.40,299.50'       from dual union
     select 'FGHIJ', '100.50,70.50,,,95.30' from dual
   ),
   -- Turn the list into a table using the comma as the delimiter. Think of it
   -- like a temp table in memory. This regex format handles NULL list elements.
   example_tbl(stock, cost) as (
     select stk, regexp_substr(cst, '(.*?)(,|$)', 1, level, NULL, 1)
     from tbl
     connect by regexp_substr(cst, '(.*?)(,|$)', 1, level) is not null
     group by stk, level, cst
   )
   -- select * from example_tbl;
   SELECT stock, to_char(sum(cost), '9990.99') Total
   from example_tbl
   group by stock;

STOCK TOTAL
----- --------
ABCDE   557.90
FGHIJ   266.30

SQL>

这篇关于在一个列中添加逗号分隔的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！