Home » SQL & PL/SQL » SQL & PL/SQL » A query for this
| A query for this [message #17291] |
Tue, 04 January 2005 15:07  |
ORCL_NEWBIE
Messages: 5
Registered: October 2002
|
Junior Member |
|
|
Hi,
I am new to Oracle. Can someone help me with this. It will be of great help
P_CD XP_CD LEVEL
1111111 2 1
1111111 1 1
1111111 3 1
1111111 21 2
1111111 13 2
1111111 11 2
1111111 12 2
1111111 32 2
1111111 23 2
1111111 22 2
1111111 31 2
1111111 39 2
1111111 132 3
1111111 131 3
1111111 124 3
1111111 129 3
I want the output like this
P_CD LEVEL XP_CD
1111111 1 2,1,3
1111111 2 21,13,11,12,32,23,22,31,39
1111111 3 132,124,129
Please help.
Thanks in advance
|
|
|
|
| Re: A query for this [message #17292 is a reply to message #17291] |
Tue, 04 January 2005 17:25  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you search these forums, you will find various examples of various methods and links to various demonstrations and explanations. I have demonstrated four of them below. Since you cannot have a column named level, because it is an Oracle reserved word, I have used lvl for the name of that column. Which method you choose depends on whether you want the concatenated values ordered or not, whether this is a one time thing or something you need to do frequently with different tables, where a generic functon would be more convenient, whether you know the maximum number of values in advance, and whether you are restricted to just sql, and which version of Oracle you are using.
-- test data:
scott@ORA92> select * from your_table
2 /
P_CD XP_CD LVL
---------- ------------------------------- ----------
1111111 2 1
1111111 1 1
1111111 3 1
1111111 21 2
1111111 13 2
1111111 11 2
1111111 12 2
1111111 32 2
1111111 23 2
1111111 22 2
1111111 31 2
1111111 39 2
1111111 132 3
1111111 131 3
1111111 124 3
1111111 129 3
16 rows selected.
-- William Robertson's method
-- (sql only and orders concatenated values)
-- (requires at least Oracle 9i)
scott@ORA92> select p_cd, lvl,
2 max (substr (sys_connect_by_path (xp_cd, ','), 2))
3 keep (dense_rank last order by curr) as xp_cds
4 from (select p_cd, lvl, xp_cd,
5 row_number () over (partition by p_cd, lvl order by xp_cd) as curr,
6 row_number () over (partition by p_cd, lvl order by xp_cd) -1 as prev
7 from your_table)
8 start with curr = 1
9 connect by prior curr = prev and prior p_cd = p_cd and prior lvl = lvl
10 group by p_cd, lvl
11 /
P_CD LVL XP_CDS
---------- ---------- ------------------------------
1111111 1 1,2,3
1111111 2 11,12,13,21,22,23,31,32,39
1111111 3 124,129,131,132
-- Tom Kyte's method
-- (generic, does not order concatenated values)
-- (requires at least Oracle 9i)
scott@ORA92> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 );
25 /
Type created.
scott@ORA92> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total
18 || ','
19 || value;
20 return ODCIConst.Success;
21 end;
22
23 member function ODCIAggregateTerminate(self IN string_agg_type,
24 returnValue OUT varchar2,
25 flags IN number)
26 return number
27 is
28 begin
29 returnValue := ltrim(self.total,',');
30 return ODCIConst.Success;
31 end;
32
33 member function ODCIAggregateMerge(self IN OUT string_agg_type,
34 ctx2 IN string_agg_type)
35 return number
36 is
37 begin
38 self.total := self.total || ctx2.total;
39 return ODCIConst.Success;
40 end;
41
42
43 end;
44 /
Type body created.
scott@ORA92> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /
Function created.
scott@ORA92> SELECT p_cd, lvl, STRAGG (xp_cd) as xp_cds
2 FROM your_table
3 GROUP BY p_cd, lvl
4 /
P_CD LVL XP_CDS
---------- ---------- ------------------------------
1111111 1 2,1,3
1111111 2 21,32,22,39,31,23,12,13,11
1111111 3 132,131,124,129
-- another method:
-- (specific to situation, orders concatenated values)
-- (works in Oracle 8i and works in 8.0 without order by clause)
scott@ORA92> create or replace function your_func
2 (p_p_cd in your_table.p_cd%type,
3 p_lvl in your_table.lvl%type)
4 return varchar2
5 as
6 v_result varchar2(32767);
7 begin
8 for rec in
9 (select xp_cd
10 from your_table
11 where p_cd = p_p_cd
12 and lvl = p_lvl
13 order by xp_cd)
14 loop
15 v_result := v_result || ',' || rec.xp_cd;
16 end loop;
17 return substr (v_result, 2);
18 end;
19 /
Function created.
scott@ORA92> show errors
No errors.
scott@ORA92> select p_cd, lvl,
2 your_func (p_cd, lvl) as xp_cds
3 from your_table
4 group by p_cd, lvl
5 /
P_CD LVL XP_CDS
---------- ---------- ------------------------------
1111111 1 1,2,3
1111111 2 11,12,13,21,22,23,31,32,39
1111111 3 124,129,131,132
-- another method
-- (requires that maximum number of concatenated values are known)
scott@ORA92> -- requires Oracle 9i
scott@ORA92> select p_cd, lvl,
2 rtrim (max (decode (rn, 1, xp_cd))
3 || ',' || max (decode (rn, 2, xp_cd))
4 || ',' || max (decode (rn, 3, xp_cd))
5 || ',' || max (decode (rn, 4, xp_cd))
6 || ',' || max (decode (rn, 5, xp_cd))
7 || ',' || max (decode (rn, 6, xp_cd))
8 || ',' || max (decode (rn, 7, xp_cd))
9 || ',' || max (decode (rn, 8, xp_cd))
10 || ',' || max (decode (rn, 9, xp_cd))
11 -- and so on until maximum number of values of xp_cd
12 , ',') as xp_cds
13 from (select p_cd, xp_cd, lvl,
14 row_number () over (partition by p_cd, lvl order by xp_cd) as rn
15 from your_table)
16 group by p_cd, lvl
17 /
P_CD LVL XP_CDS
---------- ---------- ------------------------------
1111111 1 1,2,3
1111111 2 11,12,13,21,22,23,31,32,39
1111111 3 124,129,131,132
scott@ORA92>
|
|
|
|
| Re: A query for this [message #17298 is a reply to message #17292] |
Wed, 05 January 2005 01:11 |
Padders
Messages: 79
Registered: January 2004
|
Member |
|
|
If you had 10g you could lose the GROUP BY from Will's method (and hence a sort) in favour of CONNECT_BY_ISLEAF.
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT p_cd, lvl, SUBSTR (SYS_CONNECT_BY_PATH (xp_cd, ','), 2) xp_cds
2 FROM (SELECT p_cd, lvl, xp_cd,
3 ROW_NUMBER () OVER (
4 PARTITION BY p_cd, lvl ORDER BY xp_cd) n
5 FROM your_table)
6 WHERE CONNECT_BY_ISLEAF = 1
7 START WITH n = 1
8 CONNECT BY PRIOR n + 1 = n AND PRIOR p_cd = p_cd AND PRIOR lvl = lvl;
P_CD LVL XP_CDS
---------- ---------- ----------------------------------------
1111111 1 1,2,3
1111111 2 11,12,13,21,22,23,31,32,39
1111111 3 124,129,131,132
SQL>
Mind you if you had 10g you could also consider Adrian Billington's method of using COLLECT aggregate together with a user-defined function.
SQL> CREATE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2 (4000);
2 /
Type created.
SQL> CREATE FUNCTION tabletostr (
2 p_table IN VARCHAR2_TABLE,
3 p_delimiter IN VARCHAR2 := ',')
4 RETURN VARCHAR2
5 IS
6 v_str VARCHAR2 (4000);
7 BEGIN
8 FOR i IN 1 .. p_table.COUNT LOOP
9 v_str := v_str || p_delimiter || p_table (i);
10 END LOOP;
11 RETURN v_str;
12 END;
13 /
Function created.
SQL> SELECT p_cd, lvl,
2 SUBSTR (tabletostr (CAST (
3 COLLECT (TO_CHAR (xp_cd))
4 AS VARCHAR2_TABLE)), 2) xp_cds
5 FROM your_table
6 GROUP BY p_cd, lvl;
P_CD LVL XP_CDS
---------- ---------- ----------------------------------------
1111111 1 2,1,3
1111111 2 21,13,11,12,32,23,22,31,39
1111111 3 132,131,124,129
SQL> |
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 17:29:37 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
