Concatenate Multiple Column Records

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