Oracle - Countatenate multiple rows and columns together

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
LVL 11
Wilder1626Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Please check out the function listagg to combine data from several rows.
0
 
LaccaCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Wilder1626Author Commented:
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
 
LaccaCommented:
remove ")" from line
AND A.PICK_DC = '04')
0
 
sdstuberCommented:
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
 
Wilder1626Author Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Wilder1626Author Commented:
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
 
awking00Connect With a Mentor Commented:
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
 
sdstuberCommented:
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
 
Wilder1626Author Commented:
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
 
sdstuberCommented:
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
 
Wilder1626Author Commented:
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
 
LaccaCommented:
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
 
Wilder1626Author Commented:
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
 
LaccaConnect With a Mentor Commented:
at the beginning of the post I said 2 queryes. and told to concatenate
0
 
Wilder1626Author Commented:
Thanks for all your help. Now working great.
0
All Courses

From novice to tech pro — start learning today.