transpose rows into columns (oralce query)

[Chris]

Can someone please tell me how to transpose one column of several rows into
one column by one row using an oralce 8i query.

example from this
table A
col1
row1 A
row2 B

to this
table B
col1
row1 A,B

delimator can be any char

[Boris Milrud]

Chris,

You can wrire a function, which will scan through tableA.col1 and construct
a string, and then use it to insert one row in tableB:

create or replace function BuildString (pDelimiter varchar2 := ',')
return varchar2
is
vRetVal varchar2(32757);
begin

for vRec in (select col1 as MyColumn from tableA)
loop
vRetVal := vRetVal || pDelimiter || vRec.MyColumn;
end loop;

if vRetVal is not null
then
vRetVal := substr(vRetVal, length(pDelimiter) + 1);
end if;

return vRetVal;
end BuildString;
/

Keep in mind that function could return a string up to 32757 characters long.
Now you are ready to insert:

insert into tableB (col1)
select substr(BuildString, 1, N) from Dual;

commit;

where N is the width of tableB.col1 column

Hope it helps.
Boris.


"Chris" <c.hoffmann@cqu.edu.au> wrote:
>
>Can someone please tell me how to transpose one column of several rows into
>one column by one row using an oralce 8i query.
>
>example from this
>table A
> col1
>row1 A
>row2 B
>
>to this
>table B
> col1
>row1 A,B
>
>delimator can be any char