Home » SQL & PL/SQL » SQL & PL/SQL » running string concatenation (10g)
running string concatenation [message #377176] Sun, 21 December 2008 05:57 Go to next message
kang
Messages: 89
Registered: November 2007
Member
I want to make a query to accumulate column strings or preferablly distinctly.



for example below,
the output should be

10, 1000, 20080101, 10
20, 1000, 20080102, 10/20
30, 1000, 20080103, 10/20/30
10, 1000, 20080104, 10/20/30 -- <-- 10/20/30/10 also ok though
10, 1000, 20080105, 10/20/30 -- <-- 10/20/30/10/10 also ok though
20, 1000, 20080106, 10/20/30 -- <-- 10/20/30/10/10/20 also ok though
40, 1000, 20080107, 10/20/30/40 -- <-- 10/20/30/10/10/20/40 also ok though

create table test
(item_cd varchar2(10),
vndr_cd varchar2(10),
yyyymmdd varchar2(8 )
)

insert into test(item_cd,vndr_cd,yyyymmdd) values('10','1000','20080101');
insert into test(item_cd,vndr_cd,yyyymmdd) values('20','1000','20080102');
insert into test(item_cd,vndr_cd,yyyymmdd) values('30','1000','20080103');
insert into test(item_cd,vndr_cd,yyyymmdd) values('10','1000','20080104');
insert into test(item_cd,vndr_cd,yyyymmdd) values('10','1000','20080105');
insert into test(item_cd,vndr_cd,yyyymmdd) values('20','1000','20080106');
insert into test(item_cd,vndr_cd,yyyymmdd) values('40','1000','20080107');

select item_cd,vndr_cd,yyyymmdd,
item_cd||lag(item_cd) over(partition by vndr_cd order by yyyymmdd) lag
from test;

drop table test ;

[Updated on: Sun, 21 December 2008 05:59]

Report message to a moderator

Re: running string concatenation [message #377179 is a reply to message #377176] Sun, 21 December 2008 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CONNECT BY and SYS_CONNECT_BY_PATH function.

Regards
Michel
Re: running string concatenation [message #377184 is a reply to message #377179] Sun, 21 December 2008 07:37 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
Thanks.

my query below gives just last one.

here you go.
with data
  as
  (
  select vndr_cd,
         item_cd,yyyymmdd,
         row_number() over (partition by vndr_cd order by yyyymmdd) rn,
         count(*) over (partition by vndr_cd) cnt
    from test
  )
  select vndr_cd, ltrim(sys_connect_by_path(item_cd,','),',') scbp
    from data
   where rn = cnt
   start with rn = 1
  connect by prior vndr_cd = vndr_cd and prior rn = rn-1
   order by yyyymmdd



[Edit MC: add code tags]

[Updated on: Sun, 21 December 2008 08:53] by Moderator

Report message to a moderator

Re: running string concatenation [message #377185 is a reply to message #377179] Sun, 21 December 2008 07:38 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
need some help.

Razz
Re: running string concatenation [message #377186 is a reply to message #377185] Sun, 21 December 2008 07:53 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
and this gives ora-01436 error.

with data
  as
  (
  select vndr_cd,
         item_cd,yyyymmdd,
         row_number() over (partition by vndr_cd order by yyyymmdd) rn,
         lag(item_cd) over (partition by vndr_cd order by yyyymmdd) lg
    from test
  )
  select vndr_cd, ltrim(sys_connect_by_path(item_cd,','),',') scbp
    from data  
   start with lg is null
  connect by prior item_cd = lg 


[Edit MC: add code tags, do it yourself next time]

[Updated on: Sun, 21 December 2008 08:53] by Moderator

Report message to a moderator

Re: running string concatenation [message #377188 is a reply to message #377184] Sun, 21 December 2008 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: running string concatenation [message #377190 is a reply to message #377186] Sun, 21 December 2008 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your previous query was correct, you just use something without understanding it:
SQL> with data
  2    as
  3    (
  4    select vndr_cd,
  5           item_cd,yyyymmdd,
  6           row_number() over (partition by vndr_cd order by yyyymmdd) rn,
  7           count(*) over (partition by vndr_cd) cnt
  8      from test
  9    )
 10    select vndr_cd, ltrim(sys_connect_by_path(item_cd,','),',') scbp
 11      from data
 12     where rn = cnt
 13     start with rn = 1
 14    connect by prior vndr_cd = vndr_cd and prior rn = rn-1
 15     order by yyyymmdd
 16  /
VNDR_CD    SCBP
---------- --------------------------------------------------
1000       10,20,30,10,10,20,40

1 row selected.

SQL> with data
  2    as
  3    (
  4    select vndr_cd,
  5           item_cd,yyyymmdd,
  6           row_number() over (partition by vndr_cd order by yyyymmdd) rn --,
  7  --         count(*) over (partition by vndr_cd) cnt
  8      from test
  9    )
 10    select vndr_cd, item_cd,yyyymmdd, ltrim(sys_connect_by_path(item_cd,','),',') scbp
 11      from data
 12  --   where rn = cnt
 13     start with rn = 1
 14    connect by prior vndr_cd = vndr_cd and prior rn = rn-1
 15     order by yyyymmdd
 16  /
VNDR_CD    ITEM_CD    YYYYMMDD SCBP
---------- ---------- -------- ----------------------------------
1000       10         20080101 10
1000       20         20080102 10,20
1000       30         20080103 10,20,30
1000       10         20080104 10,20,30,10
1000       10         20080105 10,20,30,10,10
1000       20         20080106 10,20,30,10,10,20
1000       40         20080107 10,20,30,10,10,20,40

7 rows selected.

Regards
Michel
Re: running string concatenation [message #377196 is a reply to message #377190] Sun, 21 December 2008 10:23 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
Thanks a lot.
what's wrong with

with data
  as
  (
  select vndr_cd,
         item_cd,yyyymmdd,
         row_number() over (partition by vndr_cd order by yyyymmdd) rn,
         lag(item_cd) over (partition by vndr_cd order by yyyymmdd) lg
    from test
  )
  select vndr_cd, ltrim(sys_connect_by_path(item_cd,','),',') scbp
    from data  
   start with lg is null
  connect by prior item_cd = lg 

[Updated on: Sun, 21 December 2008 10:24]

Report message to a moderator

Re: running string concatenation [message #377199 is a reply to message #377196] Sun, 21 December 2008 10:30 Go to previous message
kang
Messages: 89
Registered: November 2007
Member
I've got it.

Again, Thanks.
Previous Topic: PL/SQL - Using a Param both as Value and Key
Next Topic: count numbers in for loop
Goto Forum:
  


Current Time: Wed Dec 07 08:30:55 CST 2016

Total time taken to generate the page: 0.10539 seconds