Home » SQL & PL/SQL » SQL & PL/SQL » Display many rows in one row
Display many rows in one row [message #314520] |
Thu, 17 April 2008 01:05 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
hi,
i would like to display many rows into one row.
Take an simple example.
CREATE TABLE test(id NUMBER,name VARCHAR2(100),sal NUMBER);
INSERT INTO TEST ( ID, NAME, SAL ) VALUES (1, 'a', 100);
INSERT INTO TEST ( ID, NAME, SAL ) VALUES (2, 'b', 200);
INSERT INTO TEST ( ID, NAME, SAL ) VALUES (3, 'c', 100);
INSERT INTO TEST ( ID, NAME, SAL ) VALUES (4, 'd', 100);
INSERT INTO TEST ( ID, NAME, SAL ) VALUES (5, 'e', 200);
COMMIT;
And i am expecting result like,
Id Name Sal
----------------
1,3,4 a,c,d 100
2,5 b,e 200
How can i do it?
Thanks,
|
|
|
|
|
|
Re: Display many rows in one row [message #314543 is a reply to message #314541] |
Thu, 17 April 2008 02:10 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It works, but it is completely undocumented.
Oracle have no obligation to support undocumented code, or to maintain it's functionality from release to release.
Don't use that in a production system.
|
|
|
|
|
Re: Display many rows in one row [message #314741 is a reply to message #314544] |
Thu, 17 April 2008 12:28 |
|
Barbara Boehmer
Messages: 9099 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
ora_2007 wrote on Thu, 17 April 2008 00:15 |
SELECT RTRIM
( xmlagg (xmlelement (c, name || ',') ORDER BY ename).extract ('//text()')
, ',' ) AS concatenated , sal
FROM test
GROUP BY sal;
Is it ok?
Thanks,
|
You have one small mistake. Ename should be name. You probably tested on the emp table, like I do a lot, then substituted table and column names and missed one. I do like the general idea. It is pure sql, no pl/sql, very little code, no subqueries, so it is easy to write and maintain, and I am finding that a lot of the xml stuff is very efficient, so I am starting to pay more attention to it. It even allows ordering, which is something that some of the others methods either don't do or have difficulty with. The following is a slight modification of what you posted:
SCOTT@orcl_11g> CREATE TABLE test(id NUMBER,name VARCHAR2(100),sal NUMBER)
2 /
Table created.
SCOTT@orcl_11g> BEGIN
2 INSERT INTO TEST ( ID, NAME, SAL ) VALUES (1, 'a', 100);
3 INSERT INTO TEST ( ID, NAME, SAL ) VALUES (2, 'b', 200);
4 INSERT INTO TEST ( ID, NAME, SAL ) VALUES (3, 'c', 100);
5 INSERT INTO TEST ( ID, NAME, SAL ) VALUES (4, 'd', 100);
6 INSERT INTO TEST ( ID, NAME, SAL ) VALUES (5, 'e', 200);
7 END;
8 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> COMMIT
2 /
Commit complete.
SCOTT@orcl_11g> COLUMN ids FORMAT A15
SCOTT@orcl_11g> COLUMN names FORMAT A30
SCOTT@orcl_11g> SELECT RTRIM (xmlagg (xmlelement (c, id || ',') ORDER BY id).extract ('//text()') , ',' ) AS ids,
2 RTRIM (xmlagg (xmlelement (c, name || ',') ORDER BY name).extract ('//text()') , ',' ) AS names,
3 sal
4 FROM test
5 GROUP BY sal
6 /
IDS NAMES SAL
--------------- ------------------------------ ----------
1,3,4 a,c,d 100
2,5 b,e 200
SCOTT@orcl_11g>
|
|
|
Goto Forum:
Current Time: Tue Nov 05 12:58:57 CST 2024
|