Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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
0
Wilder1626
Asked:
Wilder1626
  • 7
  • 6
  • 4
  • +2
3 Solutions
 
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
 
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 
awking00Commented:
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
 
sdstuberCommented:
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
 
LaccaCommented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now