Oracle 10g multiple column string concatenation

Would it be possible to construct SQL to concatenate column values from multiple rows?

The following is an example:

Table A

PID
A
B
C

Table B

PID   SEQ    Desc

A     1      Have
A     2      a nice
A     3      day.
B     1      Nice Work.
C     1      Yes
C     2      we can 
C     3      do 
C     4      this work!

Output of the SQL should be -

PID   Desc

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

So basically the Desc column for output table is a concatenation of the SEQ values from Table B and the values are appended in the descending order of the SEQ and delimited by || ?

Any help with the SQL?

开发者_JS百科FYI - Looking for solution without using functions or stored procedures

From here

But I'd go with a function any day.

SQL> select deptno
  2       , rtrim(ename,',') enames
  3    from ( select deptno
  4                , ename
  5                , rn
  6             from emp
  7            model
  8                  partition by (deptno)
  9                  dimension by (row_number() over
 10                                (partition by deptno order by ename) rn
 11                               )
 12                  measures     (cast(ename as varchar2(40)) ename)
 13                  rules
 14                  ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
 15                  )
 16         )
 17   where rn = 1
 18   order by deptno
 19  /

    DEPTNO ENAMES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

A hierarchical query should work. A little extra trickery is needed since you want to start with the highest SEQ for each PID.

SELECT pid, fulldesc FROM (
  SELECT pid, SYS_CONNECT_BY_PATH( desc, '||' ) fulldesc, seq, minseq FROM (
    SELECT pid, seq, desc,
           MAX(seq) OVER (PARTITION BY pid) maxseq,
           MIN(seq) OVER (PARTITION BY pid) minseq
      FROM tableB
    )
    START WITH seq = maxseq
    CONNECT BY pid = PRIOR pid AND seq = PRIOR seq - 1
  )
  WHERE seq = minseq
  ORDER BY pid
  ;

Edit: One way to add a filter as requested in comment:

SELECT pid, fulldesc FROM (
  SELECT pid, SYS_CONNECT_BY_PATH( desc, '||' ) fulldesc, seq, minseq FROM (
    SELECT pid, seq, desc,
           MAX(seq) OVER (PARTITION BY pid) maxseq,
           MIN(seq) OVER (PARTITION BY pid) minseq
      FROM tableB
      WHERE pid IN (SELECT pid FROM tableB WHERE desc='day.')
    )
    START WITH seq = maxseq
    CONNECT BY pid = PRIOR pid AND seq = PRIOR seq - 1
  )
  WHERE seq = minseq
  ORDER BY pid

Here are a lot of examples on how to do it (some of them have been mentioned already) including a complete implementation of something similar to listagg():

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

You want to do something in Oracle what GROUP_CONCAT does in MySQL?

You can use WM_CONCAT if it is present: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php#wm_concat But it is undocumented, so I wouldn't use that on production, if I were you.

On 10g there is no LISTAGG yet, unfortunately.

For production environments on 10g, I would go with Dave Costa's answer.