Home » SQL & PL/SQL » SQL & PL/SQL » running string concatenation (10g)
running string concatenation [message #377176] |
Sun, 21 December 2008 05:57  |
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 #377184 is a reply to message #377179] |
Sun, 21 December 2008 07:37   |
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 #377186 is a reply to message #377185] |
Sun, 21 December 2008 07:53   |
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 #377190 is a reply to message #377186] |
Sun, 21 December 2008 08:59   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 16:54:51 CST 2025
|