OraFAQ Forum: SQL & PL/SQL » Display many rows in one row

Home » SQL & PL/SQL » SQL & PL/SQL » Display many rows in one row

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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,

Report message to a moderator

Re: Display many rows in one row [message #314523 is a reply to message #314520]

Thu, 17 April 2008 01:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is a FAQ.
Please for pivot, stragg, concat_all...

Regards
Michel

Report message to a moderator

Re: Display many rows in one row [message #314524 is a reply to message #314520]

Thu, 17 April 2008 01:23

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

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,

Report message to a moderator

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.

Report message to a moderator

Re: Display many rows in one row [message #314544 is a reply to message #314520]

Thu, 17 April 2008 02:15

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

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

Report message to a moderator

Re: Display many rows in one row [message #314741 is a reply to message #314544]

Thu, 17 April 2008 12:28

Barbara Boehmer
Messages: 9090
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 22:55:04 CDT 2024