Solved

concatenating multiple columns and multiple rows together

Posted on 2007-04-09
11
863 Views
Last Modified: 2013-12-19
I need to concatenate multiple columns from multiple rows into a data file.
For example: ID, sequence_number, data_1, data_2, data_3
1,1, DATA_1, DATA_2, DATA_3
1,2, DATA_1, DATA_2, DATA_3
1,3, DATA_1, DATA_2, DATA_3
2,1, DATA_1, DATA_2, DATA_3
3,1, DATA_1, DATA_2, DATA_3
4,1, DATA_1, DATA_2, DATA_3
4,2, DATA_1, DATA_2, DATA_3

I need the output to look like this: 1DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3

The data fields are stored as RAW(250) and I'm using utl_raw.cast_to_varchar2 then I'm using UTL_FILE to write them to a file. How do concatenate the multiple rows? This is in Oracle 10g and I have no control over the design of the table.

Thanks for your help!

0
Comment
Question by:bgrahamppg
  • 2
  • 2
  • 2
  • +2
11 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18877938
select id,
MAX(decde(id,1,col1||col2||col3,null)) id1,
MAX(decde(id,1,col1||col2||col3,null)) id2,
MAX(decde(id,1,col1||col2||col3,null)) id3
from TableA
GROUP BY id;

0
 

Author Comment

by:bgrahamppg
ID: 18878352
MohanKNair,

Thank you for responding. Your solution works great as long as I know how many rows I have.  

My problem is, there is no limit to how many rows can belong to a single id. How would I code for an undefinate amount of rows?

Thanks!
0
 

Author Comment

by:bgrahamppg
ID: 18878365
whoops, type, indefinite.
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 168 total points
ID: 18878498
You may have to use PL/SQL to handle this type of problem
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 166 total points
ID: 18880101
Use this query:

select id, replace(sys_connect_by_path(data,'/'),'/',null)
from
(select id, sequence_number, max(sequence_number) over(partition by id) mseq, utl_raw.cast_to_varchar2(data_1)||utl_raw.cast_to_varchar2(data_2)||utl_raw.cast_to_varchar2(data_3) data
from <your table>)
where sequence_number = mseq
start with sequence_number = 1
connect by prior id = id
and prior sequence_number = sequence_number - 1
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 166 total points
ID: 18890177
You can do this by creating two functions -
create or replace function concat_data_cols(id_in varchar2, seq_num_in in varchar2)
 return varchar2 is
v_text   varchar2(32767) := null;
begin
 for rec in
 (select id, data_1||data_2||data_3 data
  from datatbl where id = id_in and seq_num = seq_num_in)
 loop
 v_text := v_text||rec.data;
 end loop;
return v_text;
end;
/

create or replace function concat_data(id_in varchar2)
 return varchar2 is
v_text   varchar2(32767) := null;
begin
 for rec in
 (select id, concat_data_cols(id, seq_num) data
  from datatbl where id = id_in)
 loop
 v_text := v_text||rec.data;
 end loop;
return v_text;
end;
/
Then the following query will produce the desired results:
SQL> select id||concat_data(id) data from datatbl group by id;
DATA
------------------------------------------------------------
1DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3
0
 
LVL 3

Expert Comment

by:imaredia
ID: 18929253
Try this method, pure SQL as you are using 10g.

-- Displaying multiple column values per row
CREATE TABLE stage( customer   VARCHAR(2)
                  , product    VARCHAR(2)
                  , order_date VARCHAR2(2)
                  , OPERATOR   VARCHAR2(9)
                  );

INSERT INTO stage
     VALUES ('C1', 'P1', 'D1', 'OP1');
INSERT INTO stage
     VALUES ('C1', 'P2', 'D2', 'OP2');
INSERT INTO stage
     VALUES ('C1', 'P3', 'D3', 'OP3');
INSERT INTO stage
     VALUES ('C1', 'P1', 'D4', 'OP3');
INSERT INTO stage
     VALUES ('C2', 'P2', 'D1', 'OP2');
INSERT INTO stage
     VALUES ('C2', 'P3', 'D2', 'OP4');
INSERT INTO stage
     VALUES ('C3', 'P1', 'D2', 'OP1');
INSERT INTO stage
     VALUES ('C3', 'P1', 'D3', 'OP1');

COMMIT ;

SELECT a.customer, a.product, a.order_date, a.OPERATOR
  FROM stage a;
/*
   OUTPUT:
    customer  product  order_date  OPERATOR
    --------  -------  ----------  --------
          C1          P1              D1           OP1
          C1          P2              D2           OP2
          C1          P3              D3           OP3
          C1          P1              D4           OP3
          C2          P2              D1           OP2
          C2          P3              D2           OP4
          C3          P1              D2           OP1
          C3          P1              D3           OP1
*/

SELECT     customer,
           SUBSTR (SYS_CONNECT_BY_PATH (product, ','), 2) con_product,
           SUBSTR (SYS_CONNECT_BY_PATH (order_date, ','), 2) con_order_date,
           SUBSTR (SYS_CONNECT_BY_PATH (OPERATOR, ','), 2) con_operator
      FROM (SELECT customer, product, order_date, OPERATOR,
                   COUNT (*) OVER (PARTITION BY customer) cnt,
                   ROW_NUMBER () OVER (PARTITION BY customer
                                                   ORDER BY product, order_date, OPERATOR) seq
              FROM stage
             WHERE customer IS NOT NULL)
     WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR customer = customer;
/*
   OUTPUT:
    Customer  con_product  con_order_date  con_operator
    --------  -----------  --------------  ------------
          C1  P1,P1,P2,P3  D1,D4,D2,D3          OP1,OP3,OP2,OP3
          C2  P2,P3        D1,D2                OP2,OP4
          C3  P1,P1        D2,D3                OP1,OP1
*/

SELECT  customer, product,
        SUBSTR (SYS_CONNECT_BY_PATH (order_date, ','), 2) con_order_date,
        SUBSTR (SYS_CONNECT_BY_PATH (OPERATOR, ','), 2) con_operator
   FROM (SELECT customer, product, order_date, OPERATOR,
                COUNT (*) OVER (PARTITION BY customer, product) cnt,
                ROW_NUMBER () OVER (PARTITION BY customer, product
                                    ORDER BY order_date, OPERATOR) seq
           FROM stage
          WHERE customer IS NOT NULL
                and product  is not null)
  WHERE seq = cnt
  START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR customer = customer AND prior product = product;
/*
   OUTPUT:
    Customer  product  con_order_date  con_operator
    --------  -------  --------------  ------------
          C1          P1               D1,D4           OP1,OP3
          C1          P2               D2              OP2
          C1          P3               D3              OP3
          C2          P2               D1              OP2
          C2          P3               D2              OP4
          C3          P1               D2,D3           OP1,OP1
*/

Modify as per your requirement. Let me know if you have any questions.

Hope this helps you.
0
 
LVL 31

Expert Comment

by:awking00
ID: 22203634
It sure looks like I gave him what he wanted -
My results
1DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3

His statement
>>I need the output to look like this:<<
1DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1DATA_2DATA_3
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Value of 0's not appearing. 9 50
select query - oracle 16 82
oracle query help 36 67
Pfile and SPfile - Oracle 2 35
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now