Home » SQL & PL/SQL » SQL & PL/SQL » how to display row to column .. (9i)
how to display row to column .. [message #329750] Thu, 26 June 2008 07:31 Go to next message
vabhas
Messages: 5
Registered: June 2008
Location: INDIA
Junior Member

SQL> select ename,deptno from emp;

ENAME DEPTNO
---------- ---------
SMITH 20
ALLEN 30
WARD 30
JONES 20
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30

====================
i want this type of display...
deptno
20 smith jones scott ADAMS
30 ALLEN WARD BLAKE TURNER TURNER

any one help me
thanks for advance..
Re: how to display row to column .. [message #329751 is a reply to message #329750] Thu, 26 June 2008 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is asked several times per week, please search BEFORE posting.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: how to display row to column .. [message #329753 is a reply to message #329750] Thu, 26 June 2008 07:34 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
search for PIVOT.
Re: how to display row to column .. [message #329759 is a reply to message #329753] Thu, 26 June 2008 07:39 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
interesting but undocumented the function wm_concat() :
with a as
(
select 'SMITH' nam, 20 id from dual
union all
select 'ALLEN', 30 from dual
union all
select 'WARD', 30 from dual
union all
select 'JONES', 20 from dual
union all
select 'BLAKE', 30 from dual
union all
select 'CLARK', 10 from dual
union all
select 'SCOTT', 20 from dual
union all
select 'KING', 10 from dual
union all
select 'TURNER', 30 from dual
union all
select 'ADAMS', 20 from dual
union all
select 'JAMES', 30 from dual
)
select id, wm_concat(nam) namlist from a
group by id

gives a comma separted list:

ID	NAMLIST

10	CLARK,KING
20	SMITH,ADAMS,SCOTT,JONES
30	ALLEN,BLAKE,TURNER,JAMES,WARD
Re: how to display row to column .. [message #329762 is a reply to message #329759] Thu, 26 June 2008 07:47 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
_jum
interesting but undocumented the function wm_concat() :

Indeed; and completely (unfortunately) useless in
vabhas
how to display row to column .. (9i)

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> with a as
  2  (
  3  select 'SMITH' nam, 20 id from dual
  4  union all
  5  select 'ALLEN', 30 from dual
  6  union all
  7  select 'WARD', 30 from dual
  8  union all
  9  select 'JONES', 20 from dual
 10  union all
 11  select 'BLAKE', 30 from dual
 12  union all
 13  select 'CLARK', 10 from dual
 14  union all
 15  select 'SCOTT', 20 from dual
 16  union all
 17  select 'KING', 10 from dual
 18  union all
 19  select 'TURNER', 30 from dual
 20  union all
 21  select 'ADAMS', 20 from dual
 22  union all
 23  select 'JAMES', 30 from dual
 24  )
 25  select id, wm_concat(nam) namlist from a
 26  group by id;
select id, wm_concat(nam) namlist from a
           *
ERROR at line 25:
ORA-00904: "WM_CONCAT": invalid identifier


SQL>

Anyway, using undocumented database features isn't a very good idea in any production environment. We may use it for our own amusement, but - I wouldn't rely on something like that for business purposes.
Re: how to display row to column .. [message #329764 is a reply to message #329759] Thu, 26 June 2008 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@_jum,

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: how to display row to column .. [message #329771 is a reply to message #329764] Thu, 26 June 2008 08:11 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
Yes of course, will keep it in mind.
Such pivot problems in different forms took me a lot of hours and so I was to happy to find this simple solution (ORACLE >= 10 and wmsys) and share it Embarassed
Previous Topic: Problems with special HTML characters and PLSQL packages
Next Topic: I need an Example on this
Goto Forum:
  


Current Time: Tue Feb 11 03:43:30 CST 2025