Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
SQL query [message #157402] Thu, 02 February 2006 03:47 Go to next message
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 #157404 is a reply to message #157402] Thu, 02 February 2006 03:51 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Here's a similar thread. If you need more, I'd search for "concat_all" or "stragg".

MHE
Re: SQL query [message #157415 is a reply to message #157402] Thu, 02 February 2006 05:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Cool
http://oraclesqlpuzzle.hp.infoseek.co.jp/10-17.html
http://oraclesqlpuzzle.hp.infoseek.co.jp/10-18.html

Embarassed

OracleSQLPuzzle (sorry Japanese only)
http://oraclesqlpuzzle.hp.infoseek.co.jp
Re: SQL query [message #157542 is a reply to message #157415] Fri, 03 February 2006 02:08 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You can use "distinct" to remove duplicates.

I'll set up an example and post back.

MHE

[Updated on: Fri, 03 February 2006 02:34]

Report message to a moderator

Re: SQL query [message #157548 is a reply to message #157542] Fri, 03 February 2006 03:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #157744 is a reply to message #157728] Sun, 05 February 2006 03:32 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
zozogirl, I recommended SYS_CONNECT_BY_PATH until I did a performance check and found the concat_all solution a lot quicker. I can't find the thread back immediately, but try for yourself. SYS_CONNECT_BY_PATH is a true performance killer.

MHE
Re: SQL query [message #157749 is a reply to message #157744] Sun, 05 February 2006 05:50 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member
thanks Maaher, i'm testing your try for myself.
could you sort values delimited with '/' in second column
and tell me your modified queries?

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 Go to previous message
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

Previous Topic: Error in Dynamic SQL. Pls help!!!
Next Topic: updation
Goto Forum:
  


Current Time: Fri Mar 29 09:51:53 CDT 2024