Without Loop (merged) [message #392615] |
Wed, 18 March 2009 11:37  |
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 #392684 is a reply to message #392618] |
Wed, 18 March 2009 21:54   |
 |
Barbara Boehmer
Messages: 9104 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 #392877 is a reply to message #392727] |
Thu, 19 March 2009 09:35  |
 |
Barbara Boehmer
Messages: 9104 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>
|
|
|