Oracle - Countatenate multiple rows and columns together

Please check out the function listagg to combine data from several rows.

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

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?

remove ")" from line
AND A.PICK_DC = '04')

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)

https://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)

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

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')

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')

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.

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.

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') 

Select allOpen in new window

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.

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

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)

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.

Thanks for all your help. Now working great.