concatenating multiple columns and multiple rows together

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!

bgrahamppgAsked:
Who is Participating?
 
MohanKNairConnect With a Mentor Commented:
You may have to use PL/SQL to handle this type of problem
0
 
MohanKNairCommented:
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
 
bgrahamppgAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bgrahamppgAuthor Commented:
whoops, type, indefinite.
0
 
sujith80Connect With a Mentor Commented:
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
 
awking00Connect With a Mentor Commented:
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
 
imarediaCommented:
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
 
awking00Commented:
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
All Courses

From novice to tech pro — start learning today.