Create flat file that 'flattens' multiple rtansaction rows into one line in SQL*PLus

Question

As the title says, I need a method of flattening multiple rows into a one luine output per account. For example table looks like this:

Account Transaction

12345678 ABC

12345678 DEF

12346578 GHI

67891011 ABC

67891011 JKL

I need the output to be:

12345678|ABC|DEF|GHI

67891011|ABC|JKL

The amount of transactions is unknown. For some accounts it could be 1 or 2, all the way up to 100's.

Answer 1

You can do this using a customised version of Tom Kyte's STRAGG function, like this:

select account||'|'||stragg(transaction)
from mytable
where ...
group by account;

The function as given uses commas to separate the values, but you can easily change it to use '|'.

An example using EMP (and with commas still):

SQL> select deptno || '|' || stragg(ename) names
  2  from emp
  3  group by deptno;

NAMES
--------------------------------------------------------------------------------
10|CLARK,KING,FARMER,MILLER
20|JONES,FORD,SCOTT
30|ALLEN,TURNER,WARD,MARTIN,BLAKE