Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
( ) 1 Vote
| SQL query [message #115468] |
Mon, 11 April 2005 16:58  |
Hina
Messages: 51
Registered: April 2004
|
Member |
|
|
Table description
employee_id, role_type
001 PRESIDENT
002 ANALYST
003 CLERK
001 DIRECTOR
How to write a query, so it will display the following results from above mentioned table
results :-
employee_id role_type
001 PRESIDENT/DIRECTOR
002 ANALYST
003 CLERK
|
|
|
|
| Re: SQL query [message #115469 is a reply to message #115468] |
Mon, 11 April 2005 17:59  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Source - Barbara:
http://www.orafaq.com/msgboard/sql/messages/30023.htm
SELECT x.employee_id,
MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.role_type),'/'),2))
KEEP(DENSE_RANK LAST ORDER BY x.curr)as_string
FROM (SELECT t.employee_id,
t.role_type,ROW_NUMBER()OVER (PARTITION BY t.employee_id ORDER BY t.role_type)curr,
ROW_NUMBER()OVER (PARTITION BY t.employee_id ORDER BY t.role_type) - 1 prev
FROM t3 t) x
GROUP BY x.employee_id
START WITH x.curr = 1
CONNECT BY x.prev = PRIOR x.curr
AND x.employee_id = PRIOR x.employee_id;
EMPLOYEE_ID AS_STRING
001 DIRECTOR/PRESIDENT
002 ANALYST
003 CLERK
|
|
|
|
| Re: SQL query [message #115470 is a reply to message #115468] |
Mon, 11 April 2005 18:28 |
Hina
Messages: 51
Registered: April 2004
|
Member |
|
|
Thanks a lot, I appreciate that.
Could you tell me, how is it works.
I am trying to understand, but its lot of good stuff.
Also, if in future I have to group by some other description like 'President/Clerk' where I have to make changes
Thank you again
|
|
|
|
| Re: SQL query [message #115471 is a reply to message #115469] |
Mon, 11 April 2005 18:52 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The link provided in sverch's response was to a post by Art Metzer, that demonstrates the various methods nicely. Just to clarify where the various methods originated from, as far as I know, and provide further examples and links, and clarification:
You can use Tom Kyte's stragg function (I changed the delimiter from the original ',' to '/' to meet your requirements):
scott@ORA92> SELECT employee_id,
2 stragg (role_type) AS role_types
3 FROM employee
4 GROUP BY employee_id
5 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 PRESIDENT/DIRECTOR
002 ANALYST
003 SET
You can use James Padfield's concat_all function, which is a modification of Tom Kyte's stragg function, such that it allows you to select the delimiter as a parameter of an object, rather than hard-coding it in the function:
scott@ORA92> SELECT employee_id,
2 concat_all (concat_expr (role_type, '/')) AS role_types
3 FROM employee
4 GROUP BY employee_id
5 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 PRESIDENT/DIRECTOR
002 ANALYST
003 SET
You can use the following method which was made popular on this forum by William Robertson, who informed me that he found that, after developing the method on his own, he found that the same thing had been previously posted on Tom Kyte's site by a reader (Mikito Hari I think):
scott@ORA92> COLUMN role_types FORMAT A30
scott@ORA92> SELECT employee_id,
2 MAX (SUBSTR (SYS_CONNECT_BY_PATH (role_type, '/'), 2)) AS role_types
3 FROM (SELECT employee_id, role_type,
4 ROW_NUMBER () OVER (PARTITION BY employee_id ORDER BY role_type) AS curr,
5 ROW_NUMBER () OVER (PARTITION BY employee_id ORDER BY role_type) - 1 AS prev
6 FROM employee)
7 START WITH curr = 1
8 CONNECT BY PRIOR curr = prev AND PRIOR employee_id = employee_id
9 GROUP BY employee_id
10 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 DIRECTOR/PRESIDENT
002 ANALYST
003 SET
If you are using a version prior to 9i, you can use the following old generic method by Tom Kyte:
scott@ORA92> create or replace
2 function transpose( p_key_name in varchar2,
3 p_key_val in varchar2,
4 p_other_col_name in varchar2,
5 p_tname in varchar2,
6 p_sep in varchar2)
7 return varchar2
8 as
9 type rc is ref cursor;
10 l_str varchar2(4000);
11 l_sep varchar2(1);
12 l_val varchar2(4000);
13
14 l_cur rc;
15 begin
16
17 open l_cur for 'select '||p_other_col_name||'
18 from '|| p_tname || '
19 where ' || p_key_name || ' = :x '
20 using p_key_val;
21
22 loop
23 fetch l_cur into l_val;
24 exit when l_cur%notfound;
25 l_str := l_str || l_sep || l_val;
26 l_sep := p_sep;
27 end loop;
28 close l_cur;
29
30 return l_str;
31 end;
32 /
Function created.
scott@ORA92> SELECT employee_id,
2 transpose ('employee_id', employee_id, 'role_type', 'employee', '/')
3 AS role_types
4 FROM employee
5 GROUP BY employee_id
6 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 PRESIDENT/DIRECTOR
002 ANALYST
003 SET
You can create a function specific to your individual situation. This method has been around so long that I do not know who first thought of it or posted it:
scott@ORA92> CREATE OR REPLACE FUNCTION your_func
2 (p_employee_id IN employee.employee_id%TYPE)
3 RETURN VARCHAR2
4 AS
5 v_role_types VARCHAR2(4000);
6 BEGIN
7 FOR rec IN
8 (SELECT role_type
9 FROM employee
10 WHERE employee_id = p_employee_id
11 ORDER BY role_type)
12 LOOP
13 v_role_types := v_role_types || '/' || rec.role_type;
14 END LOOP;
15 RETURN LTRIM (v_role_types, '/');
16 END your_func;
17 /
Function created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> COLUMN role_types FORMAT A30
scott@ORA92> SELECT employee_id,
2 your_func (employee_id) AS role_types
3 FROM employee
4 GROUP BY employee_id
5 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 DIRECTOR/PRESIDENT
002 ANALYST
003 SET
William Robertson has put together a rather nice demonstration and explanation of the method that he made popular here and links to various other methods:
http://www.williamrobertson.pwp.blueyonder.co.uk/reframe.html?documents/one_row.html
Additionally, this same question was posted slightly differently on the PL/SQL Pipelines, where the impression was given that it was the information, rather than the format that was important, and that only the duplicates were sought, and William Robertson suggested the following method:
scott@ORA92> SELECT * FROM employee
2 WHERE employee_id IN
3 ( SELECT employee_id
4 FROM employee
5 GROUP BY employee_id
6 HAVING COUNT(*) > 1 )
7 /
EMPLOYEE_ID ROLE_TYPE
----------- ---------------
001 PRESIDENT
001 DIRECTOR
|
|
|
|
| Re: SQL query [message #115472 is a reply to message #115470] |
Mon, 11 April 2005 19:00 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You would just add a where condition to any of the previously provided queries, for example, if you wanted to limit the result set to those with role_type of 'PRESIDENT' or 'DIRECTOR':
scott@ORA92> SELECT * FROM employee
2 WHERE employee_id IN
3 ( SELECT employee_id
4 FROM employee
5 GROUP BY employee_id
6 HAVING COUNT(*) > 1 )
7 AND role_type IN ('PRESIDENT', 'DIRECTOR')
8 /
EMPLOYEE_ID ROLE_TYPE
----------- ---------------
001 PRESIDENT
001 DIRECTOR
scott@ORA92> SELECT employee_id,
2 stragg (role_type) AS role_types
3 FROM employee
4 WHERE role_type IN ('PRESIDENT', 'DIRECTOR')
5 GROUP BY employee_id
6 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 PRESIDENT/DIRECTOR
scott@ORA92> SELECT employee_id,
2 concat_all (concat_expr (role_type, '/')) AS role_types
3 FROM employee
4 WHERE role_type IN ('PRESIDENT', 'DIRECTOR')
5 GROUP BY employee_id
6 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 PRESIDENT/DIRECTOR
scott@ORA92> COLUMN role_types FORMAT A30
scott@ORA92> SELECT employee_id,
2 MAX (SUBSTR (SYS_CONNECT_BY_PATH (role_type, '/'), 2)) AS role_types
3 FROM (SELECT employee_id, role_type,
4 ROW_NUMBER () OVER (PARTITION BY employee_id ORDER BY role_type) AS curr,
5 ROW_NUMBER () OVER (PARTITION BY employee_id ORDER BY role_type) - 1 AS prev
6 FROM employee)
7 WHERE role_type IN ('PRESIDENT', 'DIRECTOR')
8 START WITH curr = 1
9 CONNECT BY PRIOR curr = prev AND PRIOR employee_id = employee_id
10 GROUP BY employee_id
11 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 DIRECTOR/PRESIDENT
scott@ORA92> SELECT employee_id,
2 transpose ('employee_id', employee_id, 'role_type', 'employee', '/')
3 AS role_types
4 FROM employee
5 WHERE role_type IN ('PRESIDENT', 'DIRECTOR')
6 GROUP BY employee_id
7 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 PRESIDENT/DIRECTOR
scott@ORA92> COLUMN role_types FORMAT A30
scott@ORA92> SELECT employee_id,
2 your_func (employee_id) AS role_types
3 FROM employee
4 WHERE role_type IN ('PRESIDENT', 'DIRECTOR')
5 GROUP BY employee_id
6 /
EMPLOYEE_ID ROLE_TYPES
----------- ------------------------------
001 DIRECTOR/PRESIDENT
|
|
|
|
|
|
| Re: SQL query [message #115475 is a reply to message #115468] |
Mon, 11 April 2005 19:38 |
Hina
Messages: 51
Registered: April 2004
|
Member |
|
|
I am getting this error when I am trying to execute following query.
ORA-00904: "STRAGG": invalid identifier
SELECT employee_id,
stragg (role_type) AS role_types
FROM cibc_person_role
WHERE role_type IN ('PRESIDENT', 'DIRECTOR')
GROUP BY employee_id
/
|
|
|
|
| Re: SQL query [message #115492 is a reply to message #115475] |
Mon, 11 April 2005 21:35 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You are getting the error because you did not read the responses carefully, click on the links provided, copy the code, and run it, to create the stragg function, so you have no stragg function. You have to run all of the code below to create the stragg function, then you can use it:
create or replace type string_agg_type as object
(
total varchar2(4000),
static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,
member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
);
/
create or replace type body string_agg_type
is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total
|| '/'
|| value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,'/');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/
CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 14:59:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
