?
Solved

Sql statement to concatenate different rows

Posted on 2005-05-10
6
Medium Priority
?
9,088 Views
Last Modified: 2007-12-19
I have a query which returns multiple rows for a column.  Is there any way to concatenate the rows in sql statement without creating a procedure.

Here is an example

Select col1 from table1 where col2 = 'XXXX' order by col3, col4

result set is

ABCDE
FGHIJ
KLMN
.....
.....
The number of rows can vary from 0 to 10
I need to write a view which should return one row  with

ABCDEFGHIJKLMN.......

0
Comment
Question by:dnabatra
6 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 13973252
Nothing that I can imagine...
0
 
LVL 3

Assisted Solution

by:ToddBarry
ToddBarry earned 500 total points
ID: 13973881
sql>select * from t;

COL1  COL2       COL3      COL4
----- ----- --------- ---------
ABCDE XXXX          1
FGHIJ XXXX          2
KLMNO XXXX          3
XYZ   YYYY          1
QRS   YYYY          2

5 rows selected.

sql>select replace(max(substr(sys_connect_by_path(col1, ' '), 2)), ' ') thelist
  2    from (select col1, col2,
  3                 row_number() over (partition by col2 order by col3, col4) as curr,
  4                 row_number() over (partition by col2 order by col3, col4) - 1 as prev
  5            from t
  6           where col2 = 'XXXX')
  7   start with curr = 1
  8   connect by prior curr = prev and prior col2 = col2;

THELIST
------------------------------------------------------------------------------
ABCDEFGHIJKLMNO

1 row selected.

sql>select replace(max(substr(sys_connect_by_path(col1, ' '), 2)), ' ') thelist
  2    from (select col1, col2,
  3                 row_number() over (partition by col2 order by col3, col4) as curr,
  4                 row_number() over (partition by col2 order by col3, col4) - 1 as prev
  5            from t
  6           where col2 = 'YYYY')
  7   start with curr = 1
  8   connect by prior curr = prev and prior col2 = col2;

THELIST
------------------------------------------------------------------------------
XYZQRS

1 row selected.

-- Since you want a view, you'll have to use a placeholder for the filter
-- because we are dealing with an inline view

sql>create or replace view stringlist
  2  as
  3  select replace(max(substr(sys_connect_by_path(col1, ' '), 2)), ' ') thelist
  4    from (select col1, col2,
  5                 row_number() over (partition by col2 order by col3, col4) as curr,
  6                 row_number() over (partition by col2 order by col3, col4) - 1 as prev
  7            from t
  8           where col2 = userenv('client_info'))
  9   start with curr = 1
 10   connect by prior curr = prev and prior col2 = col2;

View created.

sql>begin
  2    dbms_application_info.set_client_info('XXXX');
  3  end;
  4  /

PL/SQL procedure successfully completed.

sql>select * from stringlist;

THELIST
------------------------------------------------------------------------------
ABCDEFGHIJKLMNO

1 row selected.

sql>begin
  2    dbms_application_info.set_client_info('YYYY');
  3  end;
  4  /

PL/SQL procedure successfully completed.

sql>select * from stringlist;

THELIST
------------------------------------------------------------------------------
XYZQRS

1 row selected.
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 13974402
Use Lead() analytical function if you know the max number of rows:

I used rownum for the order by but you can use any column:

SQL> SELECT * FROM CONCAT;

COL1
----------
ABCDE
FGHIJ
KLMNO
PQRST
UVWXY
Z1234
56789
0()+=
*&%$#
<>?[]

10 rows selected.

Elapsed: 00:00:00.01
SQL> SELECT COL1 FROM(
  2  SELECT ROWNUM RN, COL1||LEAD(COL1) OVER (ORDER BY ROWNUM)||LEAD(COL1,2) OVER (ORDER BY ROWNUM)||
  3  LEAD(COL1,3) OVER (ORDER BY ROWNUM)||LEAD(COL1,4) OVER (ORDER BY ROWNUM)||
  4  LEAD(COL1,5) OVER (ORDER BY ROWNUM)||LEAD(COL1,6) OVER (ORDER BY ROWNUM)||
  5  LEAD(COL1,7) OVER (ORDER BY ROWNUM)||LEAD(COL1,8) OVER (ORDER BY ROWNUM)||
  6  LEAD(COL1,9) OVER (ORDER BY ROWNUM) COL1 FROM CONCAT)
  7  WHERE ROWNUM = 1;

COL1
----------------------------------------------------------------------------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890()+=*&%$#<>?[]

Elapsed: 00:00:00.01
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:dnabatra
ID: 13977440
Solution from ToddBarry does not work in Oracle 8i.  But seems like a good solution.

Solution from paquicuba works fine upto 4000 characters, but if it is more than 4000 charcaters, it does not work.  I get the following error.
 (Error): ORA-01489: result of string concatenation is too long

This concatemnation can have upto 20,000 characters.

Any way to increase the size?  
0
 
LVL 3

Expert Comment

by:ToddBarry
ID: 13979215
dnabatra, if you are running a version of Oracle that is two versions old and released six years ago, you need to state that in your question, otherwise it is a waste of time to provide a solution that works in all recent version of Oracle but not in your old version.
0
 

Author Comment

by:dnabatra
ID: 13983402
I will in future...

But the answer is useful because i have to do the samething 9i after a month or so

Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question