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