복붙노트

[SQL] 어떻게 오라클의 쉼표로 구분 된 목록으로 여러 행을 결합 할 수 있습니다? [복제]

SQL

어떻게 오라클의 쉼표로 구분 된 목록으로 여러 행을 결합 할 수 있습니다? [복제]

나는 간단한 쿼리를 가지고 :

select * from countries

다음과 같은 결과 :

country_name
------------
Albania
Andorra
Antigua
.....

나는이 같은, 한 행에 결과를 반환하고 싶습니다 :

Albania, Andorra, Antigua, ...

물론, 내가 일을 (이미 오라클 10g에서했던) 할 수있는 PL / SQL 함수를 작성할 수 있지만, 더 좋은, 바람직하게는 비 오라클 - 특정 솔루션이 있습니다 (또는 할 수있는 내장 기능)이 작업에 ?

나는 일반적으로 사람이 두 개 이상의 시민권이있는 경우 그래서, 그녀는 / 그 목록에서 중복되고 싶지 않아, 하위 쿼리에서 여러 행을 피하기 위해 그것을 사용할 수 있습니다.

내 질문은 SQL 서버 2005에 비슷한 질문을 기반으로합니다.

최신 정보: 내 기능은 다음과 같습니다 :

CREATE OR REPLACE FUNCTION APPEND_FIELD (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
ret varchar2(4000) := '';
TYPE cur_typ IS REF CURSOR;
rec cur_typ;
field varchar2(4000);
begin
     OPEN rec FOR sqlstr;
     LOOP
         FETCH rec INTO field;
         EXIT WHEN rec%NOTFOUND;
         ret := ret || field || sep;
     END LOOP;
     if length(ret) = 0 then
          RETURN '';
     else
          RETURN substr(ret,1,length(ret)-length(sep));
     end if;
end;

해결법

  1. ==============================

    1.여기 stragg 또는 함수를 생성하지 않고 간단한 방법이다.

    여기 stragg 또는 함수를 생성하지 않고 간단한 방법이다.

    create table countries ( country_name varchar2 (100));

insert into countries values ('Albania');

insert into countries values ('Andorra');

insert into countries values ('Antigua');


SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua                                                         

1 row selected.

    다른 언급했듯이 당신이 11g R2 이상에있는 경우, 당신은 훨씬 간단 listagg를 사용할 수 있습니다.

    select listagg(country_name,', ') within group(order by country_name) csv
  from countries;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.
  2. ==============================

    2.WM_CONCAT 기능을 잘 트릭을 할해야합니다 (오라클 11.2 시작) 또는 LISTAGG (데이터베이스에 포함 된 경우는, 오라클 11.2를 사전). 예를 들어,이 스키마에있는 테이블 이름의 쉼표로 구분 된 목록을 가져옵니다 :

    WM_CONCAT 기능을 잘 트릭을 할해야합니다 (오라클 11.2 시작) 또는 LISTAGG (데이터베이스에 포함 된 경우는, 오라클 11.2를 사전). 예를 들어,이 스키마에있는 테이블 이름의 쉼표로 구분 된 목록을 가져옵니다 :

    select listagg(table_name, ', ') within group (order by table_name) 
  from user_tables;

    또는

    select wm_concat(table_name) 
  from user_tables;

    더 많은 정보 / 옵션

    문서 링크

  3. ==============================

    3.Oracle의 경우 당신은 LISTAGG을 사용할 수 있습니다

    Oracle의 경우 당신은 LISTAGG을 사용할 수 있습니다

  4. ==============================

    4.당신은 잘으로 사용할 수 있습니다 :

    당신은 잘으로 사용할 수 있습니다 :

    SELECT RTRIM (
          XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
          ',')
          country_name
  FROM countries;
  5. ==============================

    5.이 쿼리를 시도 할 수 있습니다.

    이 쿼리를 시도 할 수 있습니다.

    select listagg(country_name,',') within group (order by country_name) cnt 
from countries;
  6. ==============================

    6.가장 빠른 방법은 오라클 수집 기능을 사용하는 것입니다.

    가장 빠른 방법은 오라클 수집 기능을 사용하는 것입니다.

    또한이 작업을 수행 할 수 있습니다

    select *
  2    from (
  3  select deptno,
  4         case when row_number() over (partition by deptno order by ename)=1
  5             then stragg(ename) over
  6                  (partition by deptno
  7                       order by ename
  8                         rows between unbounded preceding
  9                                  and unbounded following)
 10         end enames
 11    from emp
 12         )
 13   where enames is not null

    톰 물어 사이트를 방문하여 'stragg'또는 '문자열 연결'을 검색하십시오. 많은 예. 사용자의 요구를 달성하기 위해하지 문서화 오라클 기능도 있습니다.

  7. ==============================

    7.나는 비슷한 일을 필요로 다음과 같은 해결책을 발견했다.

    나는 비슷한 일을 필요로 다음과 같은 해결책을 발견했다.

    select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from
  8. ==============================

    8.이 예에서 우리는 헤더 수준의 쿼리에 대한 하나 개의 필드에 별개의 라인 레벨 AP 송장 보류 이유의 쉼표로 구분 된 목록을 가져올에 함수를 만드는 :

    이 예에서 우리는 헤더 수준의 쿼리에 대한 하나 개의 필드에 별개의 라인 레벨 AP 송장 보류 이유의 쉼표로 구분 된 목록을 가져올에 함수를 만드는 :

     FUNCTION getHoldReasonsByInvoiceId (p_InvoiceId IN NUMBER) RETURN VARCHAR2

  IS

  v_HoldReasons   VARCHAR2 (1000);

  v_Count         NUMBER := 0;

  CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
   IS
     SELECT DISTINCT hold_reason
       FROM ap.AP_HOLDS_ALL APH
      WHERE status_flag NOT IN ('R') AND invoice_id = p2_InvoiceId;
BEGIN

  v_HoldReasons := ' ';

  FOR rHR IN v_HoldsCusror (p_InvoiceId)
  LOOP
     v_Count := v_COunt + 1;

     IF (v_Count = 1)
     THEN
        v_HoldReasons := rHR.hold_reason;
     ELSE
        v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
     END IF;
  END LOOP;

  RETURN v_HoldReasons;
END;
  9. ==============================

    9.나는 항상이에 대한 몇 가지 PL / SQL을 작성했다 아니면 단지 CONCATENATE에 ','현장 및 편집기로 복사 나에게 한 줄을 제공 목록에서 CR을 제거했다.

    나는 항상이에 대한 몇 가지 PL / SQL을 작성했다 아니면 단지 CONCATENATE에 ','현장 및 편집기로 복사 나에게 한 줄을 제공 목록에서 CR을 제거했다.

    그건,

    select country_name||', ' country from countries

    조금 긴 두 가지를 호흡.

    톰 물어 보면 당신은 가능한 해결책의 부하를 볼 수 있지만, 그들은 모두 되돌리기 및 / 또는 PL / SQL 선언을 입력

    톰 질문

  10. ==============================

    10.

    SELECT REPLACE(REPLACE
((SELECT     TOP (100) PERCENT country_name + ', ' AS CountryName
FROM         country_name
ORDER BY country_name FOR XML PATH('')), 
'&<CountryName>', ''), '&<CountryName>', '') AS CountryNames
  11. ==============================

    11.당신은 위의 작업을 수행하는이 쿼리를 사용할 수 있습니다

    당신은 위의 작업을 수행하는이 쿼리를 사용할 수 있습니다

    DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test

    단계 설명에 의한 세부 사항 및 단계에 대한 다음의 링크를 방문 http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html

    12. from https://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버에서 날짜 시간 대 DATETIME2  (0) 2020.03.11
[SQL] MySQL의 : 테이블을 만들 수 없습니다 (errno를 : 150)  (0) 2020.03.11
[SQL] LIMIT의 등가 및 SQL Server에 대한 OFFSET?  (0) 2020.03.11
[SQL] 어떻게 오라클에서 시퀀스를 재설정합니까?  (0) 2020.03.11
[SQL] SQL 서버에서 오프셋 행  (0) 2020.03.11
,

티스토리툴바