Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
SQL query [message #157402] |
Thu, 02 February 2006 03:47 |
sxramas
Messages: 16 Registered: January 2006 Location: Chennai
|
Junior Member |
|
|
Hello,
The following is the input rows 1 A
1 A
1 B
2 B
2 B
2 B
3 A
3 A
I want the output as follows:
1 A/B
2 B
3 A
Can you please give me the SQL query to use to get the required output?
Please let me know if you need additional information.
Regards,
Siva
|
|
|
|
Re: SQL query [message #157415 is a reply to message #157402] |
Thu, 02 February 2006 05:59 |
sxramas
Messages: 16 Registered: January 2006 Location: Chennai
|
Junior Member |
|
|
Hi ,
Thanks for the answer to the query.Actually that query gives all the values. for eg
If input is 1 A
1 A
1 B
The output will be 1 A/A/B
I want the output as 1 A/B
Please reply
Thanks in advance
Regards,
Siva
|
|
|
Re: SQL query [message #157519 is a reply to message #157415] |
Thu, 02 February 2006 22:52 |
aketi
Messages: 26 Registered: October 2005
|
Junior Member |
|
|
with KeyList as(
select 1 as Key,'A' as Val from dual
union select 1,'B' from dual
union select 2,'B' from dual
union select 2,'B' from dual
union select 2,'B' from dual
union select 3,'A' from dual
union select 3,'A' from dual)
select Key,SubStr(sys_connect_by_path(Val,','),2) as ConcatVal
from (select Key,Val,
Row_Number() over(partition by Key order by Val) as Rank,
count(Key) over(partition by Key) as MaxLevel
from (select distinct * from KeyList))
where Level = MaxLevel
start with Rank = 1
connect by Prior Key = Key
and Prior Rank = Rank-1
order by Key;
another SQL
http://oraclesqlpuzzle.hp.infoseek.co.jp/10-17.html
http://oraclesqlpuzzle.hp.infoseek.co.jp/10-18.html
OracleSQLPuzzle (sorry Japanese only)
http://oraclesqlpuzzle.hp.infoseek.co.jp
|
|
|
|
Re: SQL query [message #157548 is a reply to message #157542] |
Fri, 03 February 2006 03:12 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
As promised. I used the script below to test:
PROMPT Object creation
CREATE TABLE mhe_foo(col1 NUMBER, col2 VARCHAR2(1))
/
INSERT INTO mhe_foo VALUES (1, 'A')
/
INSERT INTO mhe_foo VALUES (1, 'A')
/
INSERT INTO mhe_foo VALUES (1, 'B')
/
INSERT INTO mhe_foo VALUES (2, 'B')
/
INSERT INTO mhe_foo VALUES (2, 'B')
/
INSERT INTO mhe_foo VALUES (2, 'B')
/
INSERT INTO mhe_foo VALUES (3, 'A')
/
INSERT INTO mhe_foo VALUES (3, 'A')
/
INSERT INTO mhe_foo VALUES (3, 'B')
/
INSERT INTO mhe_foo VALUES (3, 'B')
/
CREATE OR REPLACE TYPE concat_expr AS OBJECT (
str VARCHAR2 (4000),
del VARCHAR2 (4000)
);
/
CREATE OR REPLACE TYPE concat_all_ot AS OBJECT (
str VARCHAR2 (4000),
del VARCHAR2 (4000),
STATIC FUNCTION odciaggregateinitialize (
sctx IN OUT concat_all_ot)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT concat_all_ot,
ctx IN concat_expr)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (
SELF IN concat_all_ot,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT concat_all_ot,
ctx2 concat_all_ot)
RETURN NUMBER);
/
CREATE OR REPLACE TYPE BODY concat_all_ot
AS
STATIC FUNCTION odciaggregateinitialize (
sctx IN OUT concat_all_ot)
RETURN NUMBER
IS
BEGIN
sctx := concat_all_ot (NULL, NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT concat_all_ot,
ctx IN concat_expr)
RETURN NUMBER
IS
BEGIN
IF SELF.str IS NOT NULL THEN
SELF.str := SELF.str || ctx.del;
END IF;
SELF.str := SELF.str || ctx.str;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate (
SELF IN concat_all_ot,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
returnvalue := SELF.str;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT concat_all_ot,
ctx2 IN concat_all_ot)
RETURN NUMBER
IS
BEGIN
IF SELF.str IS NOT NULL THEN
SELF.str := SELF.str || SELF.del;
END IF;
SELF.str := SELF.str || ctx2.str;
RETURN odciconst.success;
END;
END;
/
CREATE OR REPLACE FUNCTION concat_all (
ctx IN concat_expr)
RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
AGGREGATE USING concat_all_ot;
/
PROMPT First example (no distinct)
SELECT col1
, concat_all(concat_expr(col2,'/')) col2
FROM mhe_foo
GROUP BY col1
/
PROMPT Second example (with distinct)
SELECT col1
, concat_all(concat_expr(distinct(col2),'/')) col2
FROM mhe_foo
GROUP BY col1
/
PROMPT This doesn't work and it is known as bug #3263979
PROMPT We apply the workaround from the bug document
SELECT col1
, concat_all(concat_expr(col2,'/')) col2
FROM ( SELECT DISTINCT
col1
, col2
FROM mhe_foo
)
GROUP BY col1
/
PROMPT cleanup
DROP TABLE mhe_foo
/
DROP FUNCTION concat_all
/
DROP TYPE concat_all_ot
/
DROP TYPE concat_expr
/
When I ran it, I got the following result:
SQL> @C:\useful\orafaq
Object creation
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Type created.
Type created.
Type body created.
Function created.
First example (no distinct)
COL1
----------
COL2
--------------------------------------------------------------------------------
1
A/A/B
2
B/B/B
3
A/B/B/A
Second example (with distinct)
, concat_all(concat_expr(distinct(col2),'/')) col2
*
ERROR at line 2:
ORA-30482: DISTINCT option not allowed for this function
This doesn't work and it is known as bug #3263979
We apply the workaround from the bug document
COL1
----------
COL2
--------------------------------------------------------------------------------
1
A/B
2
B
3
A/B
cleanup
Table dropped.
Function dropped.
Type dropped.
Type dropped.
MHE
|
|
|
Re: SQL query [message #157728 is a reply to message #157519] |
Sat, 04 February 2006 22:07 |
zozogirl
Messages: 77 Registered: November 2005 Location: Seoul, Korea
|
Member |
|
|
aketi's query is not good enough.
try this.
SELECT KEY, SUBSTR (MAX (SYS_CONNECT_BY_PATH (val, '/')), 2) concatval
FROM (SELECT KEY, val,
ROW_NUMBER () OVER (PARTITION BY KEY ORDER BY val) rnum
FROM keylist
GROUP BY KEY, val)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR KEY = KEY
GROUP BY KEY
for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
|
|
|
|
|
Re: SQL query [message #157793 is a reply to message #157749] |
Mon, 06 February 2006 01:24 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
First I have to rectify myself. I said a DISTINCT wouldn't work directly. Well, I was a bit off here. If you defined a MAP function in the scalar type concat_expr like this:
CREATE OR REPLACE TYPE concat_expr AS OBJECT (
str VARCHAR2 (4000),
del VARCHAR2 (4000),
MAP MEMBER FUNCTION f_map RETURN VARCHAR2);
/
CREATE OR REPLACE TYPE BODY concat_expr AS
MAP MEMBER FUNCTION f_map
RETURN VARCHAR2
IS
BEGIN
RETURN str;
END f_map;
END;
/ you can distinct the values like this:
SELECT col1
, concat_all(distinct(concat_expr(col2,'/'))) col2
FROM mhe_foo
GROUP BY col1
/
Ah well, we all make mistakes.
MHE
[Updated on: Mon, 06 February 2006 01:25] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Fri Mar 29 09:51:53 CDT 2024
|