[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

concatenating multiple columns and multiple rows together

Posted on 2007-04-09
11
Medium Priority
?
896 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Accepted Solution

by:
MohanKNair earned 672 total points
ID: 18878498
You may have to use PL/SQL to handle this type of problem
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 664 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 32

Assisted Solution

by:awking00
awking00 earned 664 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 32

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

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.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

649 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