[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

concatenating multiple columns and multiple rows together

Posted on 2007-04-09
11
Medium Priority
?
900 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
8 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

830 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