Use the wmsys. wm_concat function to convert rows and columns.

Introduction of wmsys

Wmssys is used to store all the metadata information for Oracle Workspace Manager. this user was introduced in Oracle9i and (like most Oracle9i supporting accounts) is locked by default. the user account is locked because we want the password to be public but restrict access to the account to the Sys schema. so, to unlock the account, DBA privileges are required.

This post will show you on how to use the method wmssys. wm_concat to convert the row and columns in data table.

SQL> select version from V $ instance;

Version
-----------------
10.2.0.1.0

SQL>
SQL> Create Table idtable
2 (
3 ID number,
4 Val varchar2 (20)
5)
6;

Table created

SQL>
SQL> insert into idtable (ID, Val)
2 values (10, 'abc ');

1 row inserted
SQL> insert into idtable (ID, Val)
2 values (10, 'abc ');

1 row inserted
SQL> insert into idtable (ID, Val)
2 values (10, 'def ');

1 row inserted
SQL> insert into idtable (ID, Val)
2 values (10, 'def ');

1 row inserted
SQL> insert into idtable (ID, Val)
2 values (20, 'ghi ');

1 row inserted
SQL> insert into idtable (ID, Val)
2 values (20, 'jkl ');

1 row inserted
SQL> insert into idtable (ID, Val)
2 values (20, 'mno ');

1 row inserted
SQL> insert into idtable (ID, Val)
2 values (20, 'mno ');

1 row inserted

SQL> select ID, Val from idtable;

Id Val
------------------------------
10 ABC
10 ABC
10 def
10 def
20 Ghi
20 jkl
20 MnO
20 MnO

8 rows selected

SQL> commit;

Commit complete

SQL>
SQL> select ID, Wmsys. wm_concat (VAL) As enames
2 from idtable
3 group by ID;

Id enames
------------------------------------------------------------------------------------------
10 ABC, ABC, def, def
20 ghi, jkl, MnO, MnO

SQL>
SQL> select ID, wmsys. wm_concat (distinct Val) as enames
2 from idtable
3 group by ID
4 order by ID;

Id enames
------------------------------------------------------------------------------------------
10 ABC, def
20 ghi, jkl, MnO

SQL>
SQL> select ID, Val, wmsys. wm_concat (VAL) over (partition by ID) as enames
2 from idtable
3 order by ID;

Id Val enames
--------------------------------------------------------------------------------------------------------------
10 ABC, ABC, def, def
10 ABC, ABC, def, def
10 def ABC, ABC, def, def
10 def ABC, ABC, def, def
20 Ghi ghi, jkl, MnO, MnO
20 jkl ghi, jkl, MnO, MnO
20 MnO ghi, jkl, MnO, MnO
20 MnO ghi, jkl, MnO, MnO

8 rows selected

SQL>
SQL> select ID, Val, wmsys. wm_concat (VAL) over (order by ID, Val) as enames
2 from idtable
3 order by ID;

Id Val enames
--------------------------------------------------------------------------------------------------------------
10 ABC, ABC
10 ABC, ABC
10 def ABC, ABC, def, def
10 def ABC, ABC, def, def
20 Ghi ABC, ABC, def, def, Ghi
20 jkl ABC, ABC, def, def, Ghi, jkl
20 MnO ABC, ABC, def, def, Ghi, jkl, MnO, MnO
20 MnO ABC, ABC, def, def, Ghi, jkl, MnO, MnO

8 rows selected