|
|
|
Re: how to display row to column .. [message #329759 is a reply to message #329753] |
Thu, 26 June 2008 07:39   |
_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   |
 |
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   |
 |
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  |
_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
|
|
|