glufke.net

Selecionar tudo

Tip of the Week
Tip for Week of October 16, 2006

Concatenate Multiple Column Records

This tip comes from Galina Petrenko, a Sr. Programmer Analyst at Harte-Hanks Inc. in Langhorne, PA.

In Oracle9i Database and later, it's possible to rotate the vertical presentation of data into horizontal presentation. The following demonstrates a simple way (without XML parsers or PL/SQL stored structures) to convert vertical presentation into horizontal presentation; it will concatenate multiple column records into a single string.

-- Temporary Data

create table TMP_TEST ( ID NUMBER, NumVal NUMBER, STRVAL VARCHAR2(32) );

insert into TMP_TEST values(1, 100, 'Hello');
insert into TMP_TEST values(1,-100, 'World');
insert into TMP_TEST values(2, 1, 'Concatenate');
insert into TMP_TEST values(2, 2, 'In String');
insert into TMP_TEST values(2, 3, 'using Connect By');
insert into TMP_TEST values(2, 4, 'Using SYS_CONNECT_BY_PATH');

commit;

-- Check Vertical Data Presentation

select * from TMP_TEST;

ID         NUMVAL     STRVAL
---------- ---------- --------------------------------
1          100        Hello
1          -100       World
2          1          Concatenate
2          2          In String
2          3          using Connect By
2          4          Using SYS_CONNECT_BY_PATH

6 rows selected.

-- Get Concatenated String with Specific Delimiter

def Delimiter=" "

SELECT
ID,
SUBSTR(MAX(REPLACE(
SYS_CONNECT_BY_PATH(STRVAL, '/')
,'/','&Delimiter')),2) Concatenated_String
FROM (
select A.*,
row_number() OVER (Partition by ID order by ID) ROW#
from TMP_TEST A)
START WITH ROW#=1
CONNECT BY PRIOR ID=ID AND PRIOR row# = row# -1
GROUP BY ID
;

ID          CONCATENATED_STRING
----------  -----------------------------------------------------------------
1           Hello World
2           Concatenate In String using Connect By Using SYS_CONNECT_BY_PATH

-- More Generic:

def Column_Sort_Name=ID
def Column_Name=NumVal
def Delimiter="|"

SELECT
ID,
SUBSTR(MAX(REPLACE(
SYS_CONNECT_BY_PATH( &Column_Name , '/')
,'/','&Delimiter')),2) Concatenated_String
FROM (
select
A.*,
row_number() OVER (Partition by ID order by &Column_Sort_Name ) ROW#
from TMP_TEST A)
START WITH ROW#=1
CONNECT BY PRIOR &Column_Sort_Name = &Column_Sort_Name
AND PRIOR row# = row# -1
GROUP BY &Column_Sort_Name
;


ID          CONCATENATED_STRING
----------  -----------------------------------------------------------------
1           100|-100
2           1|2|3|4



def Delimiter=","


ID          CONCATENATED_STRING
----------  -----------------------------------------------------------------
1           100,-100
2           1,2,3,4

Selecionar tudo

row_number() OVER (Partition by ID order by &Column_Sort_Name )