Oracle에서 여러 행의 열 값을 연결하는 SQL 쿼리

Oracle에서 여러 행의 열 값을 연결하는 SQL 쿼리

---

여러 행의 열 값을 연결하기 위해 SQL을 구성 할 수 있습니까?

다음은 예입니다.

표 A

PID
ㅏ
비
씨

표 B

PID 시퀀스 설명

1 가지고
2 좋은
3 일
B 1 좋은 일.
C 1 예
우리가 할 수있는 C 2 
C 3 할 
C 4이 작품!

SQL의 출력은 다음과 같아야합니다.

PID 설명
A 좋은 하루 되세요.
B : 잘 했어.
C : 우리는이 일을 할 수 있습니다!

따라서 기본적으로 출력 테이블의 설명 열은 테이블 B의 SEQ 값을 연결 한 것입니까?

SQL에 대한 도움이 필요하십니까?

---

사용중인 버전에 따라 몇 가지 방법이 있습니다 . 문자열 집계 기술에 대한 Oracle 설명서를 참조하십시오 . 가장 일반적인 방법은 다음을 사용하는 것입니다 LISTAGG.

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;

그런 다음에 가입 A밖으로 선택하는 pids당신이 원하는.

참고 : 기본적 LISTAGG으로 VARCHAR2열 에서만 올바르게 작동합니다 .

---

XMLAGG11.2 이전 버전에서 작동 하는 기능 도 있습니다 . OracleWM_CONCAT 은 문서화하지 않고 지원하지 않기 때문에 프로덕션 시스템에서는 사용하지 않는 것이 좋습니다.

함께 XMLAGG사용하면 다음을 수행 할 수 있습니다 :

SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result" 
FROM employee_names

이것이하는 일은

• 테이블 의 ename열 값 (쉼표로 연결)을 employee_namesxml 요소 (태그 E 포함)에 넣습니다.
• 이 텍스트를 추출
• XML 집계 (연결)
• 결과 열을 "결과"호출

---

SQL 모델 절에서 :

SQL> select pid
  2       , ltrim(sentence) sentence
  3    from ( select pid
  4                , seq
  5                , sentence
  6             from b
  7            model
  8                  partition by (pid)
  9                  dimension by (seq)
 10                  measures (descr,cast(null as varchar2(100)) as sentence)
 11                  ( sentence[any] order by seq desc
 12                    = descr[cv()] || ' ' || sentence[cv()+1]
 13                  )
 14         )
 15   where seq = 1
 16  /

P SENTENCE
- ---------------------------------------------------------------------------
A Have a nice day
B Nice Work.
C Yes we can do this work!

3 rows selected.

I wrote about this here. And if you follow the link to the OTN-thread you will find some more, including a performance comparison.

---

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. If you are using 11g Release 2 you should use this function for string aggregation. Please refer below url for more information about string concatenation.

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

String Concatenation

---

As most of the answers suggest, LISTAGG is the obvious option. However, one annoying aspect with LISTAGG is that if the total length of concatenated string exceeds 4000 characters( limit for VARCHAR2 in SQL ), the below error is thrown, which is difficult to manage in Oracle versions upto 12.1

ORA-01489: result of string concatenation is too long

A new feature added in 12cR2 is the ON OVERFLOW clause of LISTAGG. The query including this clause would look like:

SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

The above will restrict the output to 4000 characters but will not throw the ORA-01489 error.

These are some of the additional options of ON OVERFLOW clause:

• ON OVERFLOW TRUNCATE 'Contd..' : This will display 'Contd..' at the end of string (Default is ... )
• ON OVERFLOW TRUNCATE '' : This will display the 4000 characters without any terminating string.
• ON OVERFLOW TRUNCATE WITH COUNT : This will display the total number of characters at the end after the terminating characters. Eg:- '...(5512)'
• ON OVERFLOW ERROR : If you expect the LISTAGG to fail with the ORA-01489 error ( Which is default anyway ).

---

For those who must solve this problem using Oracle 9i (or earlier), you will probably need to use SYS_CONNECT_BY_PATH, since LISTAGG is not available.

To answer the OP, the following query will display the PID from Table A and concatenate all the DESC columns from Table B:

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
       FROM (
              SELECT a.pid, seq, description
              FROM table_a a, table_b b
              WHERE a.pid = b.pid(+)
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

There may also be instances where keys and values are all contained in one table. The following query can be used where there is no Table A, and only Table B exists:

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
       FROM (
              SELECT pid, seq, description
              FROM table_b
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

All values can be reordered as desired. Individual concatenated descriptions can be reordered in the PARTITION BY clause, and the list of PIDs can be reordered in the final ORDER BY clause.

---

Alternately: there may be times when you want to concatenate all the values from an entire table into one row.

The key idea here is using an artificial value for the group of descriptions to be concatenated.

In the following query, the constant string '1' is used, but any value will work:

SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY unique_id ORDER BY pid, seq) rnum, description
       FROM (
              SELECT '1' unique_id, b.pid, b.seq, b.description
              FROM table_b b
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1;

Individual concatenated descriptions can be reordered in the PARTITION BY clause.

Several other answers on this page have also mentioned this extremely helpful reference: https://oracle-base.com/articles/misc/string-aggregation-techniques

---

1. LISTAGG delivers the best performance if sorting is a must(00:00:05.85)

   SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description FROM B GROUP BY pid;

2. COLLECT delivers the best performance if sorting is not needed(00:00:02.90):

   SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;

3. COLLECT with ordering is bit slower(00:00:07.08):

   SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;

All other techniques were slower.

---

Before you run a select query, run this:

SET SERVEROUT ON SIZE 6000

SELECT XMLAGG(XMLELEMENT(E,SUPLR_SUPLR_ID||',')).EXTRACT('//text()') "SUPPLIER" 
FROM SUPPLIERS;

---

I using the LISTAGG but return this string for persian string !

my query:

SELECT
 listagg(DESCRIPTION,' , ') within group (order by DESCRIPTION) 
FROM
B_CEREMONY

result:

'A7'1 , ,4F

Please help me.

wow this solution is worked:

SELECT listagg(convert(DESCRIPTION, 'UTF8', 'AL16UTF16'),' , ') within group 
(order by DESCRIPTION) 
FROM  B_CEREMONY;

---

Try this code:

 SELECT XMLAGG(XMLELEMENT(E,fieldname||',')).EXTRACT('//text()') "FieldNames"
    FROM FIELD_MASTER
    WHERE FIELD_ID > 10 AND FIELD_AREA != 'NEBRASKA';

---

In the select where you want your concatenation, call a SQL function.

For example:

select PID, dbo.MyConcat(PID)
   from TableA;

Then for the SQL function:

Function MyConcat(@PID varchar(10))
returns varchar(1000)
as
begin

declare @x varchar(1000);

select @x = isnull(@x +',', @x, @x +',') + Desc
  from TableB
    where PID = @PID;

return @x;

end

The Function Header syntax might be wrong, but the principle does work.

참고URL : https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle