Toad World® Forums

concatenation in toad challenge.


#1

Good Evening

kindly I need assistance in concatenation of the data below as shown in green.

select ORDER_NO,ITEM_CODE from ORDERS_DETAILS where ORDER_NO=‘5059550826’

For Example

ORDER_NO |ITEM_CODE

5059550826
HPCEI,HPCQC,HPCEI,HPCEI,A110X

ORDER_NO
ITEM_CODE

5059550826
HPCEI

5059550826
HPCQC

5059550826
A110X

5059550826
HPCEI

5059550826
HPCEI

5059550826
A110X

5059550826
P210E

5059550826
HPCPC

5059550826
HPCPC

5059550826
AC250

5059550826
HPCEI

5059550826
DELLI

5059550826
TOSHL

5059550826
HPCEI

5059550826
TOSHL

5059550826
TOSHL

5059550826
189

5059550826
123

5059550826
135

5059550826
156

5059550826
144

5059550826
172

5059550826
100

5059550826
104

5059550826
116

5059550826
121

5059550826
127

5059550826
10

5059550826
104

5059550826
104


#2

select ORDER_NO || ITEM_CODE from ORDERS_DETAILS where ORDER_NO=‘5059550826’


#3

Not sure you can do that with a SQL statement. You need to write a function to
create the concatenated string with the commas.


#4

Bert - I think the OP wants to pivot the item_code column at the same time as
well.

Charles – one option is to look into using a pipelined function. That way
you can convert multiple input rows into one output row.

Mike
smime.p7s (5.14 KB)


#5

Thanks Bert ,

but the challenge is having only one row.

that is the order_no and the respective item_codes separated by a comma.

5059550826
HPCEI,HPCQC,HPCEI,HPCEI,A110X


#6

Charles,

It is not so much a “Toad” challenge as it is an “Oracle” challenge.

Your going to have to create a custom function to return the pivot data
the way you want it. I created a simple one for you. I would query
this from your ORDERS table and not the detail so you won’t need to use
DISTINCT to get one row back.

CREATE OR REPLACE FUNCTION get_order_detail_items(pOrderNo NUMBER)
RETURN VARCHAR2 IS
vRet VARCHAR2(4000);
BEGIN

FOR x IN (SELECT item_code FROM orders_details WHERE order_no =

pOrderNo)
LOOP
IF vRet IS NULL
THEN
vRet := x.item_code;
ELSE
vRet := vRet||’,’||x.item_code;
END IF;
END LOOP;

RETURN vRet;

END get_order_detail_items;

then issue the select

SELECT DISTINCT order_no, get_order_detail_items(order_no)
FROM orders_details

I use the distinct so only one row returns but it still does the
function call 30 times so that is why you would want to run this from
the parent table.

You can also use a pipeline function too. I would go to
http://asktom.oracle.com and query for pivot to find more examples.

Ed
[TeamT]

On 7/13/2010 12:12 PM, charles ndirangu wrote:

Good Evening
kindly I need assistance in concatenation of the data below as shown in
green.
select ORDER_NO,ITEM_CODE from ORDERS_DETAILS where ORDER_NO=‘5059550826’
For Example
ORDER_NO |ITEM_CODE

    • 5059550826 HPCEI,HPCQC,HPCEI,HPCEI,A110X

ORDER_NO ITEM_CODE
5059550826 HPCEI
5059550826 HPCQC
5059550826 A110X
5059550826 HPCEI
5059550826 HPCEI
5059550826 A110X
5059550826 P210E
5059550826 HPCPC
5059550826 HPCPC
5059550826 AC250
5059550826 HPCEI
5059550826 DELLI
5059550826 TOSHL
5059550826 HPCEI
5059550826 TOSHL
5059550826 TOSHL
5059550826 189
5059550826 123
5059550826 135
5059550826 156
5059550826 144
5059550826 172
5059550826 100
5059550826 104
5059550826 116
5059550826 121
5059550826 127
5059550826 10
5059550826 104
5059550826 104


#7

An all SQL solution:

with order_details as
(
select 1 order_no, ‘ABC’ item_code from dual union all
select 1 order_no, ‘DEF’ item_code from dual union all
select 1 order_no, ‘GHI’ item_code from dual union all
select 2 order_no, ‘JKL’ item_code from dual union all
select 2 order_no, ‘MNO’ item_code from dual union all
select 3 order_no, ‘PQR’ item_code from dual
)
select
order_no,
substr(sys_connect_by_path(item_code, ‘,’ ),2) item_codes
from
(
select
order_no,
item_code,
row_number() over (partition by order_no order by item_code) rn
from order_details
)
where connect_by_isleaf = 1
connect by (rn = prior rn+1) and (order_no = prior order_no)
start with rn = 1
order by order_no
;

Modify the ‘where’ clause to filter on a specific ‘order_no’ per your original
example.


#8

below is the function i have tried but am getting this errors,

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

CREATE OR REPLACE FUNCTION get_tbacust.order_details(pDetOrdOrderNo NUMBER)
RETURN VARCHAR2 IS
vRet VARCHAR2(4000);
BEGIN

FOR x IN (SELECT DET_SUP_ITEM_CODE FROM tbacust.order_details WHERE
DET_ORD_ORDER_NO =
pDetOrdOrderNo)
LOOP
IF vRet IS NULL
THEN
vRet := x.DET_SUP_ITEM_CODE;
ELSE
vRet := vRet||’,’||x.DET_SUP_ITEM_CODE;
END IF;
END LOOP;

RETURN vRet;

begin
SELECT DISTINCT DET_ORD_ORDER_NO, get_tbacust.order_details(DET_ORD_ORDER_NO)
FROM tbacust.order_details;
return (DET_ORD_ORDER_NO);
end get_tbacust.order_details;
/

&&&&&&&&&&&&&&&&&&&&&&

errors

ERROR line 23, col 16, ending_line 23, ending_col 16, Found ‘.’, Expecting: ;


#9

Fortunately, Tom Kyte’s already blazed that trail. Google “STRAGG”, which
should eventually take you here:

http://asktom.oracle.com/pls/asktom
/f?p=100:11:0::::P11_QUESTION_ID:229614022562

Enjoy!
Rich – [TeamT]

Bert - I think the OP wants to pivot the item_code column at the same time
as well.

Charles - one option is to look into using a pipelined function. That way
you can convert multiple input rows into one output row.

Mike


#10

This can be done with the PIVOT operator. The downside is you must have a fixed
number of item_code values defined per row in the result set. Google “oracle
pivot” for details.

  • Pete J.

Pflucker, Mario wrote:

Not sure you can do that with a SQL statement. You need to write a function
to create the concatenated string with the commas.


#11

Aha – I believe if you check the last 3 or 4 issues of Oracle magazine that
either the Ask Tom or another SQL type article had this very example ….


#12

Yes – that was the function in Oracle magazine very recently – with
a great explanation of how it works (and the source code)


#13

Darn, I was just about to offer that one – but I didn’t know about
the connect_by_isleaf function, that’s cool.

If you can put a reasonably small upper bound on the number of rows for a given
key (i.e. for a given order_no in this case), I have found code like the
following to be more efficient for large tables (assuming an upper bound of ten
in this example):

with order_details as

(

select 1 order_no , ‘ABC’ item_code from dual union all

select 1 order_no , ‘DEF’ item_code from dual union all

select 1 order_no , ‘GHI’ item_code from dual union all

select 2 order_no , ‘JKL’ item_code from dual union all

select 2 order_no , ‘MNO’ item_code from dual union all

select 3 order_no , ‘PQR’ item_code from dual

)

select order_no

, max(decode( rn , 1 , item_code )) ||

max(decode( rn , 2 , ‘,’ || item_code )) ||

max(decode( rn , 3 , ‘,’ || item_code )) ||

max(decode( rn , 4 , ‘,’ || item_code )) ||

max(decode( rn , 5 , ‘,’ || item_code )) ||

max(decode( rn , 6 , ‘,’ || item_code )) ||

max(decode( rn , 7 , ‘,’ || item_code )) ||

max(decode( rn , 8 , ‘,’ || item_code )) ||

max(decode( rn , 9 , ‘,’ || item_code )) ||

max(decode( rn , 10 , ‘,’ || item_code )) item_codes

from (select order_no ,

item_code ,

row_number () over (partition by order_no order by item_code ) rn

from order_details )

group by order_no

order by order_no

Nate Schroeder

US Seed & Trait Commercial IT - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592


#14

You may have already recieved a better answer and

this may be a little archaic for some of the advanced users out there but it
works and I understand it. (smile).

Run the script below by itself furst to make sure it works, modify it whatever.

Used it to make the table test1.

Then run the second script.

Good luck.

Gene

CREATE TABLE TEST1 AS

select order_no , max(case when item_code = ‘HPCEI’ then
ITEM_CODE end) as COL1,

                     max(case when item_code =

‘HPCQCI’ then ITEM_CODE end) as COL2,

                    max(case when item_code =

‘P210EI’ then ITEM_CODE end) as COL3,

                    max(case when item_code =

‘AC250’ then ITEM_CODE end) as COL4,

                   max(case when item_code =

‘DELLI’ then ITEM_CODE end) as COL5,

                  max(case when item_code =

‘TOSHL’ then ITEM_CODE end) as COL6

from (
select order_no, item_code

  from order_details 
  where item_code in ('HPCEI', 'HPCQCI' , 

‘P210EI’, ‘AC250’ , ‘DELLI’ , ‘TOSHL’ ))

SELECT order_no||col1||col2||col3||col4||col5

from test1

On Tue, Jul 13, 2010 at 1:51 PM, SCHROEDER, NATHAN E [AG/1000] <
nathan.e.schroeder@monsanto.com > wrote:

Darn, I was just about to offer that one – but I didn’t know about the
connect_by_isleaf function, that’s cool.

 

If you can put a reasonably small upper bound on the number of rows for a
given key (i.e. for a given order_no in this case), I have found code like
the following to be more efficient for large tables (assuming an upper bound
of ten in this example):

 

with order_details as

   (

   select 1 order_no , 'ABC' item_code from dual union all

   select 1 order_no , 'DEF' item_code from dual union all

   select 1 order_no , 'GHI' item_code from dual union all

   select 2 order_no , 'JKL' item_code from dual union all

   select 2 order_no , 'MNO' item_code from dual union all

   select 3 order_no , 'PQR' item_code from dual

   )

select order_no

     , max(decode( rn , 1 , item_code )) ||

       max(decode( rn , 2 , ',' || item_code )) ||

       max(decode( rn , 3 , ',' || item_code )) ||

       max(decode( rn , 4 , ',' || item_code )) ||

       max(decode( rn , 5 , ',' || item_code )) ||

       max(decode( rn , 6 , ',' || item_code )) ||

       max(decode( rn , 7 , ',' || item_code )) ||

       max(decode( rn , 8 , ',' || item_code )) ||

       max(decode( rn , 9 , ',' || item_code )) ||

       max(decode( rn , 10 , ',' || item_code )) item_codes

  from (select order_no ,

               item_code ,

               row_number () over (partition by order_no order
by item_code ) rn

          from order_details )

  group by order_no

  order by order_no

 

Nate Schroeder

US Seed & Trait Commercial IT - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592

#15

Charles

ERROR line 23, col 16, ending_line 23, ending_col 16, Found ‘.’,
Expecting: ;

To answer your question about this error is simple. You cannot have
an invalid character in the name of a FUNCTION. A “.” is invalid syntax
for a function name. Try an underscore “get_tbacust_order_details”

CREATE OR REPLACE FUNCTION get_tbacust.order_details(pDetOrdOrderNo
NUMBER)

Ed
[TeamT]

On 7/13/2010 2:01 PM, charles ndirangu wrote:

below is the function i have tried but am getting this errors,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE OR REPLACE FUNCTION get_tbacust.order_details(pDetOrdOrderNo NUMBER)
RETURN VARCHAR2 IS
vRet VARCHAR2(4000);
BEGIN
FOR x IN (SELECT DET_SUP_ITEM_CODE FROM tbacust.order_details WHERE
DET_ORD_ORDER_NO =
pDetOrdOrderNo)
LOOP
IF vRet IS NULL
THEN
vRet := x.DET_SUP_ITEM_CODE;
ELSE
vRet := vRet||’,’||x.DET_SUP_ITEM_CODE;
END IF;
END LOOP;
RETURN vRet;
begin
SELECT DISTINCT DET_ORD_ORDER_NO,
get_tbacust.order_details(DET_ORD_ORDER_NO)
FROM tbacust.order_details;
return (DET_ORD_ORDER_NO);
end get_tbacust.order_details;
/

&&&&&&&&&&&&&&&&&&&&&&
errors
ERROR line 23, col 16, ending_line 23, ending_col 16, Found ‘.’,
Expecting: ;