bgrahamppg
asked on
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_1D
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1D
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!
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_1D
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1D
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!
ASKER
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!
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!
ASKER
whoops, type, indefinite.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
-- 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.
It sure looks like I gave him what he wanted -
My results
1DATA_1DATA_2DATA_3DATA_1D
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1D
His statement
>>I need the output to look like this:<<
1DATA_1DATA_2DATA_3DATA_1D
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1D
My results
1DATA_1DATA_2DATA_3DATA_1D
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1D
His statement
>>I need the output to look like this:<<
1DATA_1DATA_2DATA_3DATA_1D
2DATA_1DATA_2DATA_3
3DATA_1DATA_2DATA_3
4DATA_1DATA_2DATA_3DATA_1D
MAX(decde(id,1,col1||col2|
MAX(decde(id,1,col1||col2|
MAX(decde(id,1,col1||col2|
from TableA
GROUP BY id;