Home » SQL & PL/SQL » SQL & PL/SQL » Without Loop (merged)
Without Loop (merged) [message #392615] Wed, 18 March 2009 11:37 Go to next message
bhabani_bal
Messages: 1
Registered: November 2008
Junior Member
Hi Friend,
I need your help vvery badly.There is a requrement like
sql> desc tn_table;
tn_code   tn_type
--------  -------
T1        BAT
T1        Wicket
T1        Light
T2        BALL
T3        Dress
T3        Bag


our output is to be
tn_code                        tn_type
--------                       -------
T1,BAT,Wicket,Light            Light
T2                             BALL
T3,Dress,Bag                   Bag


if for same tn_code there is more than one tn_type then append the tn_code by comma.

Without using any loop can we do this . because loop takes a lots of time. The table contains more than 100 000 records.

kindly give me some suggestions.
Thankins in advance

[Mod-edit: Frank added code-tags]

[Updated on: Thu, 19 March 2009 01:45] by Moderator

Report message to a moderator

Re: Without Loop [message #392618 is a reply to message #392615] Wed, 18 March 2009 11:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/345288/69655/?srch=stragg#msg_345288

Always please search before posting.
This is a frequently asked question.
You may want to tweak a little for to get the "last repeat append".

[Updated on: Wed, 18 March 2009 11:45]

Report message to a moderator

Re: Without Loop [message #392619 is a reply to message #392615] Wed, 18 March 2009 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do not cross/multi-post
Re: Without Loop [message #392684 is a reply to message #392618] Wed, 18 March 2009 21:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Here is one more method that wasn't mentioned in that link.

SCOTT@orcl_11g> CREATE TABLE tn_table
  2    (tn_code  VARCHAR2 (7),
  3  	tn_type  VARCHAR2 (7))
  4  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO tn_table VALUES ('T1', 'BAT')
  3  INTO tn_table VALUES ('T1', 'Wicket')
  4  INTO tn_table VALUES ('T1', 'Light')
  5  INTO tn_table VALUES ('T2', 'BALL')
  6  INTO tn_table VALUES ('T3', 'Dress')
  7  INTO tn_table VALUES ('T3', 'Bag')
  8  SELECT * FROM DUAL
  9  /

6 rows created.

SCOTT@orcl_11g> COLUMN tn_type FORMAT A30
SCOTT@orcl_11g> SELECT tn_code,
  2  	    RTRIM
  3  	      (XMLAGG (XMLELEMENT (C, tn_type || ',') ORDER BY ROWID).EXTRACT ('//text ()'),
  4  	      ',' ) AS tn_type
  5  FROM   tn_table
  6  GROUP  BY tn_code
  7  /

TN_CODE TN_TYPE
------- ------------------------------
T1      BAT,Wicket,Light
T2      BALL
T3      Dress,Bag

SCOTT@orcl_11g>

Re: Without Loop [message #392727 is a reply to message #392684] Thu, 19 March 2009 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I added code-tags around the desired output in the original post.
I can't see the logic of it..
Re: Without Loop [message #392877 is a reply to message #392727] Thu, 19 March 2009 09:35 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It looks different with the code tags. I previously thought some things that were in the first column were in the second column. So, it would be something more like below, although there needs to be something more reliable than rowid to get the last tn_type, such as a date or numeric column. There would also need to be some modification if you don't want BALL in the first column.

SCOTT@orcl_11g> COLUMN tn_code FORMAT A30
SCOTT@orcl_11g> COLUMN tn_type FORMAT A15
SCOTT@orcl_11g> SELECT tn_code || ',' || wm_concat (tn_type) AS tn_code,
  2  	    MAX (tn_type) KEEP (DENSE_RANK LAST ORDER BY ROWID) AS tn_type
  3  FROM   tn_table
  4  GROUP  BY tn_code
  5  ORDER  BY tn_code
  6  /

TN_CODE                        TN_TYPE
------------------------------ ---------------
T1,BAT,Wicket,Light            Light
T2,BALL                        BALL
T3,Dress,Bag                   Bag

SCOTT@orcl_11g>

Previous Topic: How to acheive this logic in single procedure? (merged)
Next Topic: Return a Row from a Function
Goto Forum:
  


Current Time: Fri Dec 09 05:55:31 CST 2016

Total time taken to generate the page: 0.13950 seconds