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 Go to next message
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 #314523 is a reply to message #314520] Thu, 17 April 2008 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ.
Please for pivot, stragg, concat_all...

Regards
Michel
Re: Display many rows in one row [message #314524 is a reply to message #314520] Thu, 17 April 2008 01:23 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
search for the keyword pivot, stragg in this forum or google it. You will find loads of answers how to do it.

Regards

Raj

p.s : As always, you are quicker. Smile

[Updated on: Thu, 17 April 2008 01:27]

Report message to a moderator

Re: Display many rows in one row [message #314541 is a reply to message #314520] Thu, 17 April 2008 01:59 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
This works for me.
SELECT wmsys.wm_concat(name),sal FROM test
GROUP BY sal;


Thanks,
Re: Display many rows in one row [message #314543 is a reply to message #314541] Thu, 17 April 2008 02:10 Go to previous messageGo to next message
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 #314544 is a reply to message #314520] Thu, 17 April 2008 02:15 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
SELECT RTRIM
       ( xmlagg (xmlelement (c, name || ',') ORDER BY ename).extract ('//text()')
       , ',' ) AS concatenated , sal
FROM   test
GROUP BY sal;


Is it ok?

Thanks,

[Updated on: Thu, 17 April 2008 02:15]

Report message to a moderator

Re: Display many rows in one row [message #314553 is a reply to message #314544] Thu, 17 April 2008 02:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this link. It is just to give you an idea about the performance of different solutions.

I do know that it is doing exactly the opposite of what you are doing. But it could give you some idea or the different alternatives you could potentially use.

http://www.oracle-developer.net/display.php?id=412

Hope that helps

Regards

Raj


Re: Display many rows in one row [message #314741 is a reply to message #314544] Thu, 17 April 2008 12:28 Go to previous message
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> 




Previous Topic: Time taken for script
Next Topic: Error at line 26 (no matter what)
Goto Forum:
  


Current Time: Tue Nov 05 12:58:57 CST 2024