Solved

Oracle - Countatenate multiple rows and columns together

Posted on 2014-02-12
19
480 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
  • 7
  • 6
  • 4
  • +2
19 Comments
 
LVL 142

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
 
LVL 1

Expert Comment

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

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 73

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 73

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

Assisted Solution

by:awking00
awking00 earned 50 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 73

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 73

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 73

Accepted Solution

by:
sdstuber earned 300 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 150 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now