Solved

concatenating multiple columns and multiple rows together

Posted on 2007-04-09
11
867 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
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 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
 
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 32

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 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

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.

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

774 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