?
Solved

Oracle - Countatenate multiple rows and columns together

Posted on 2014-02-12
19
Medium Priority
?
496 Views
Last Modified: 2014-02-15
Hello

I would like to know how i can concatenate multiple columns and rows based on my below SQL:

SELECT 
    A.PICK_DC AS DC, 
    A.STORE_NUM, 
    C.PICK_DC, 
    C.RECEIVE_DC, 
    C.SHIP_DAY AS RELEASE_DAY, 
    C.ARRIVE_DAY, 
    C.RECEIVE_ARRIVE_OPEN, 
    C.RECEIVE_ARRIVE_CLOSE
FROM 
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C 
WHERE 
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM 
    AND B.LANE_NUM= C.LANE_NUM 
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04';

Open in new window



When i normally query the above SQL, the result i get is in 8 columns:

04      01550      04      31      1      1      1430      1730
04      01550      04      31      2      2      1430      1730
04      01550      04      31      3      3      1430      1730
04      01550      04      31      4      4      1430      1730
04      01550      04      31      5      5      1430      1730
04      01550      04      31      6      6      1430      1730
04      01550      04      31      7      7      1430      1730


What i need to do is to concatenate column 5, 6, 7, 8 together.

So at the end, with the above example, it would give me:

1,1,1430,1730,2,2,1430,1730,3,3,1430,1730,4,4,1430,1730,5,5,1430,1730,6,6,1430,1730,7,7,1430,1730

Open in new window


So instead of 7 rows in my result, i would get only 1 but with the result:
04      01550      04      31      1,1,1430,1730,2,2,1430,1730,3,3,1430,1730,4,4,1430,1730,5,5,1430,1730,6,6,1430,1730,7,7,1430,1730

FYI - Number of row can vary based on below values:
AND A.STORE_NUM = '01550'
AND A.PICK_DC = '04'

How can i do that?

Thanks again for your help
0
Comment
Question by:Wilder1626
[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
  • 7
  • 6
  • 4
  • +2
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39854137
Please check out the function listagg to combine data from several rows.
0
 
LVL 1

Expert Comment

by:Lacca
ID: 39854191
Hi,

I propose two querys, and then you concatenate de results of them

NOTE: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value.

--1st:

SELECT distinct
        A.PICK_DC AS DC,
        A.STORE_NUM,
        C.PICK_DC,
        C.RECEIVE_DC
FROM
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
WHERE
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04';


--2nd:
with t as (select col
from
(select C.SHIP_DAY AS RELEASE_DAY||','||
        C.ARRIVE_DAY||','||
        C.RECEIVE_ARRIVE_OPEN||','||
        C.RECEIVE_ARRIVE_CLOSE col
from  
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
where
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04')
group by A.PICK_DC AS DC,
        A.STORE_NUM,
        C.PICK_DC,
        C.RECEIVE_DC,
        C.SHIP_DAY AS RELEASE_DAY||','||
        C.ARRIVE_DAY||','||
        C.RECEIVE_ARRIVE_OPEN||','||
        C.RECEIVE_ARRIVE_CLOSE))
SELECT LTRIM(col,'|') output
FROM (SELECT MAX(SYS_CONNECT_BY_PATH(col,'|')) KEEP(DENSE_RANK LAST ORDER BY col) col
FROM (SELECT col, ROW_NUMBER() OVER(ORDER BY col) curr,
                  ROW_NUMBER() OVER(ORDER BY col) -1 prev
      FROM t)
CONNECT BY PRIOR curr = prev  
START WITH curr = 1)


Regards,
Lacca
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39854200
Hi Lacca

When i run the second query, i have thisError at line 1
ORA-00923: FROM keyword not found where expected

Would you know why?
0
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
LVL 1

Expert Comment

by:Lacca
ID: 39854233
remove ")" from line
AND A.PICK_DC = '04')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39854295
SYS_CONNECT_BY_PATH is the worst performing and most expensive of the common methods for doing string concatenation.

Use LISTAGG as suggested above if you are on 11gR2 or higher.

If not on11gR2, but 9i or higher, you could try building your own aggregator as show here... (search for concat_agg)

http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html


or, use xml aggregation


or if 10gR2  use COLLECT function and write function to read collection and return concatednated string  (search EE for str2tbl for several examples)
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39854308
Hi sdstuber

I'm with Oracle 9i. I will take a look at your link. Thanks.

Lacca

I'm still having this error:

with t as (select col
from
(select C.SHIP_DAY AS RELEASE_DAY||','||
        C.ARRIVE_DAY||','||
        C.RECEIVE_ARRIVE_OPEN||','||
        C.RECEIVE_ARRIVE_CLOSE col
from  
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
where
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04'
group by A.PICK_DC AS DC,
        A.STORE_NUM,
        C.PICK_DC,
        C.RECEIVE_DC,
        C.SHIP_DAY AS RELEASE_DAY||','||
        C.ARRIVE_DAY||','||
        C.RECEIVE_ARRIVE_OPEN||','||
        C.RECEIVE_ARRIVE_CLOSE))
SELECT LTRIM(col,'|') output
FROM (SELECT MAX(SYS_CONNECT_BY_PATH(col,'|')) KEEP(DENSE_RANK LAST ORDER BY col) col
FROM (SELECT col, ROW_NUMBER() OVER(ORDER BY col) curr,
                  ROW_NUMBER() OVER(ORDER BY col) -1 prev
      FROM t)
CONNECT BY PRIOR curr = prev  
START WITH curr = 1)
                                 *
Error at line 3
ORA-00923: FROM keyword not found where expected
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39854329
9i is pretty old, I don't remember if xml aggregation worked back then or not.
Also, depending on the exact release, the sys_connect_by_path method had errors as well


You could try this for xml


SELECT RTRIM(
           EXTRACT(
               XMLAGG(
                   XMLELEMENT(
                       "x",
                          release_day
                       || ','
                       || arrive_day
                       || ','
                       || receive_arrive_open
                       || ','
                       || receive_arrive_close
                       || ','
                   )
                   ORDER BY
                       dc,
                       store_num,
                       pick_dc,
                       receive_dc
               ),
               '/x/text()'
           ).getstringval(),
           ','
       )
           x
  FROM (SELECT
    A.PICK_DC AS DC,
    A.STORE_NUM,
    C.PICK_DC,
    C.RECEIVE_DC,
    C.SHIP_DAY AS RELEASE_DAY,
    C.ARRIVE_DAY,
    C.RECEIVE_ARRIVE_OPEN,
    C.RECEIVE_ARRIVE_CLOSE
FROM
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
WHERE
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39854336
If you do build the concat_agg in the article, the usage is pretty easy...


SELECT concat_agg(
              release_day
           || ','
           || arrive_day
           || ','
           || receive_arrive_open
           || ','
           || receive_arrive_close
       )
           x
  FROM  (SELECT
    A.PICK_DC AS DC,
    A.STORE_NUM,
    C.PICK_DC,
    C.RECEIVE_DC,
    C.SHIP_DAY AS RELEASE_DAY,
    C.ARRIVE_DAY,
    C.RECEIVE_ARRIVE_OPEN,
    C.RECEIVE_ARRIVE_CLOSE
FROM
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
WHERE
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04')
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39854363
wow, you are right, much more cleaner on post: ID: 39854336

The thing is that i have this error: A-00904: "CONCAT_AGG": invalid identifier.

But for post ID: 39854329, looks like it's working.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 200 total points
ID: 39854375
It seems to me that this worked way back when -
create or replace function agg4cols(p_dc in varchar2)
return varchar2 is
v_txt varchar2(32767) := null;
begin
 for rec in
(SELECT
    A.PICK_DC AS DC,
    A.STORE_NUM,
    C.PICK_DC,
    C.RECEIVE_DC,
    C.SHIP_DAY AS RELEASE_DAY||','||C.ARRIVE_DAY||','||C.RECEIVE_ARRIVE_OPEN||','|| C.RECEIVE_ARRIVE_CLOSE as last4cols
FROM
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
WHERE
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04')
 loop
  v_txt := v_txt||','||rec.last4cols;
 end loop;
 return ltrim(v_txt,',');
end;
/
select dc, store_num, pick_dc, receive_dc, agg4cols(dc) agg4cols
from
(SELECT
    A.PICK_DC AS DC,
    A.STORE_NUM,
    C.PICK_DC,
    C.RECEIVE_DC,
    C.SHIP_DAY AS RELEASE_DAY||','||C.ARRIVE_DAY||','||C.RECEIVE_ARRIVE_OPEN||','|| C.RECEIVE_ARRIVE_CLOSE as last4cols
FROM
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
WHERE
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04')
group by dc, store_num, pick_dc, receive_dc;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39854382
CONCAT_AGG": invalid identifier.


did you build the concat_agg type and function as shown in the article?
it's not a built-in function, you have to create it.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39854564
ok, i will read your topic carefully.

Now, when i used the below SQL, the concatenation is working great.

I'm just missing the below columns before the X column
 A.PICK_DC AS DC,
 A.STORE_NUM,
 C.PICK_DC,
 C.RECEIVE_DC


I Just can seems to find out where to add them in the query.

SELECT RTRIM(
           EXTRACT(
               XMLAGG(
                   XMLELEMENT(
                       "x",
                          release_day
                       || ','
                       || arrive_day
                       || ','
                       || receive_arrive_open
                       || ','
                       || receive_arrive_close
                       || ','
                   )
                   ORDER BY
                       dc,
                       store_num,
                       pick_dc,
                       receive_dc
               ),
               '/x/text()'
           ).getstringval(),
           ','
       )
           x
  FROM (SELECT
    A.PICK_DC AS DC,
    A.STORE_NUM,
    C.PICK_DC,
    C.RECEIVE_DC,
    C.SHIP_DAY AS RELEASE_DAY,
    C.ARRIVE_DAY,
    C.RECEIVE_ARRIVE_OPEN,
    C.RECEIVE_ARRIVE_CLOSE
FROM
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
WHERE
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04') 

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39854640
given your sample data above, what would you expect the output to be if you included those columns?

the expected output in the question only showed the concatenation of the last 4 columns.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39854656
if i go back to my first post, if i have the below result normally:


04      01550      04      31      1      1      1430      1730
04      01550      04      31      2      2      1430      1730
04      01550      04      31      3      3      1430      1730
04      01550      04      31      4      4      1430      1730
04      01550      04      31      5      5      1430      1730
04      01550      04      31      6      6      1430      1730
04      01550      04      31      7      7      1430      1730

i should now have:

04      01550      04      31     1,1,1430,1730,2,2,1430,1730,3,3,1430,1730,4,4,1430,1730,5,5,1430,1730,6,6,1430,1730,7,7,1430,1730
0
 
LVL 1

Expert Comment

by:Lacca
ID: 39854894
Now I thin it works.

with t as (select col
from
(select C.SHIP_DAY||','||
        C.ARRIVE_DAY||','||
        C.RECEIVE_ARRIVE_OPEN||','||
        C.RECEIVE_ARRIVE_CLOSE col
from  
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
where
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04'
group by A.PICK_DC,
        A.STORE_NUM,
        C.PICK_DC,
        C.RECEIVE_DC,
        C.SHIP_DAY||','||
        C.ARRIVE_DAY||','||
        C.RECEIVE_ARRIVE_OPEN||','||
        C.RECEIVE_ARRIVE_CLOSE))
SELECT LTRIM(col,'|') output
FROM (SELECT MAX(SYS_CONNECT_BY_PATH(col,'|')) KEEP(DENSE_RANK LAST ORDER BY col) col
FROM (SELECT col, ROW_NUMBER() OVER(ORDER BY col) curr,
                  ROW_NUMBER() OVER(ORDER BY col) -1 prev
      FROM t)
CONNECT BY PRIOR curr = prev  
START WITH curr = 1)
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39854908
Hi Lacca

I like the formatting:
1,1,1430,1730|2,2,1430,1730|3,3,1430,1730|4,4,1430,1730|5,5,1430,1730|6,6,1430,1730|7,7,1430,1730.

But i still don't see the first 4 columns before the concatenation Outpout.

Result
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1200 total points
ID: 39854954
SELECT dc,
         store_num,
         pick_dc,
         receive_dc,
         RTRIM(
             EXTRACT(
                 XMLAGG(
                     XMLELEMENT(
                         "x",
                            release_day
                         || ','
                         || arrive_day
                         || ','
                         || receive_arrive_open
                         || ','
                         || receive_arrive_close
                         || ','   --- change this to '|' if you want bars between the sets
                     )
                     ORDER BY
                         release_day,
                         arrive_day,
                         receive_arrive_open,
                         receive_arrive_close
                 ),
                 '/x/text()'
             ).getstringval(),
             ','    --- change this to '|' if you want bars between the sets
         )
             x
    FROM (SELECT
    A.PICK_DC AS DC,
    A.STORE_NUM,
    C.PICK_DC,
    C.RECEIVE_DC,
    C.SHIP_DAY AS RELEASE_DAY,
    C.ARRIVE_DAY,
    C.RECEIVE_ARRIVE_OPEN,
    C.RECEIVE_ARRIVE_CLOSE
FROM
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
WHERE
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0
    AND A.STORE_NUM = '01550'
    AND A.PICK_DC = '04')
GROUP BY dc,
         store_num,
         pick_dc,
         receive_dc


note, I changed the order by in the aggregate too.  The ordering didn't make sense if used as a grouping criteria.
0
 
LVL 1

Assisted Solution

by:Lacca
Lacca earned 600 total points
ID: 39855077
at the beginning of the post I said 2 queryes. and told to concatenate
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 39861926
Thanks for all your help. Now working great.
0

Featured Post

A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
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 how to recover a database from a user managed backup
Suggested Courses

801 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