在Oracle中添加逗号(，) [英] add a comma (,) in Oracle

问题描述

给出此查询:

select distinct subject_key
from mytable

结果:

subject_key
-----------
90896959
90895823
90690171
90669265
90671321

我该如何在Oracle中编写查询(使用Aqua Data Studio后端Oracle 8i)结果:

How do i write a query in Oracle (using Aqua Data Studio backend Oracle 8i) result:

subject_key
-----------
90896959,
90895823,
90690171,
90669265,
90671321

谢谢大家！ 我是否应该改变输出而不是像下面那样降低输出.我如何在同一平台上编写它.谢谢.

THANKS ALL! Should I wish to change the output across instead of down like below. How do I write it, same platform. Thanks.

subject_key
90896959,  90895823, 90690171,  90669265, 90671321

推荐答案

Oracle没有像MySQL的GROUP_CONCAT这样的功能，而这正是您要的功能. 此页面上提供了各种用于此类字符串聚合的选项-一个是为了使用自定义功能:

Oracle doesn't have a function like MySQL's GROUP_CONCAT, which is exactly the functionality you're asking for. Various options for such string aggregation are provided on this page - one is to use a custom function:

CREATE OR REPLACE FUNCTION get_subjectkey (IN_PK IN MYTABLE.PRIMARY_KEY%TYPE)
RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN

  FOR cur_rec IN (SELECT subject_key 
                    FROM MYTABLE 
                   WHERE primary_key = IN_PK) LOOP
    l_text := l_text || ',' || cur_rec.ename;
  END LOOP;

  RETURN LTRIM(l_text, ',');
END;

然后您将使用它:

SELECT get_subjectkey(?) AS subject_key
  FROM DUAL

...替换?"并带有主键值.

...replacing the "?" with the primary key value.

假设您只想在列值的末尾添加逗号，请使用:

Assuming you just want to add a comma to the end of the column value, use:

SELECT DISTINCT TO_CHAR(subject_key) || ','
  FROM MYTABLE

双管道-"||" -是Oracle [，PostgreSQL，现在是ANSI]在SQL中串联字符串的方法.我使用TO_CHAR显式转换数据类型，但是您可以使用:

The double pipe -- "||" -- is the Oracle [,PostgreSQL and now ANSI] means of concatenating strings in SQL. I used TO_CHAR to explicitly convert the data type, but you could use:

SELECT DISTINCT subject_key || ','
  FROM MYTABLE

...如果不需要的话.

...if that's not necessary.

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