Pivot Table Documentation example: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#CHDFIIDD Syntax diagram: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#CHDCEJJE Tom Kyte's examples: http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28asktom-087592.html ---------------------------------------------------------------------------------------------------- Transposition (as in Excel, paste-special with transpose option) Tom Kyte's cols_as_rows function: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:249321700346816237 If you don't mind transposing the result for each row separately, Lou Fangxin's asql (http://www.anysql.net/anysql/) works wonders! Note the \g (or \G) at the end of the query. I'm sure MySQL users find it familiar. $ ls -l -rw-r--r-- 1 oracle oinstall 156674 May 23 14:51 oasql.jar -rw-r--r-- 1 oracle oinstall 1536554 May 23 14:51 oracle.jar $ CLASSPATH=./oasql.jar $ORACLE_HOME/jdk/bin/java com.asql.tools.ASQL AnySQL for Oracle(8i/9i/10g), Release 3.0.0 (Build:20060816-1013) (@) Copyright Lou Fangxin 2004/2005, all rights reserved. ASQL> conn yhuang@dbhostname:1521/dbservicename Password: Database connected. ASQL> select * from user_users\g; USERNAME : YHUANG USER_ID : 89 ACCOUNT_STATUS : OPEN LOCK_DATE : null EXPIRY_DATE : null DEFAULT_TABLESPACE : USERS TEMPORARY_TABLESPACE : TEMP CREATED : 2008-04-09 15:03:22.0 INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP EXTERNAL_NAME : null 1 rows returned. ASQL> exit Disconnect from database! You can download asql at http://www.anysql.net/software/anysql.zip The files are asql.exe asqlw.exe jlib jlib\jtds.jar jlib\oasql.jar jlib\oracle.jar jlib\swt.jar swt-win32-3063.dll Only oasql.jar and oracle.jar are really needed. On Windows I have to use the SID syntax to connect (I have java.exe in my %PATH%). C:\>cd anysql\jlib C:\anysql\jlib>java -cp oasql.jar com.asql.tools.ASQL ... ASQL> conn yhuang@dbhostname:1521/dbservicename Password: Io exception: Invalid connection string format, a valid format is: "host:port:sid" ASQL> conn yhuang@dbhostname:1521:ORCL Password: Database connected. The asql.exe on Windows works too: C:\anysql\jlib>cd.. C:\anysql>asql ... ASQL> conn yhuang@dbhostname:1521:ORCL Password: Database connected. You have to have JRE on the machine to use it. Obviously, your application server can't run this tool-specific SQL with the \g SQL statement terminator. ---------------------------------------------------------------------------------------------------- String Concatenation SQL> select * from a; ID NAME ---------- ---------- 1 David 2 Yong SQL> select * from b; ID SKILL ---------- -------------------- 1 Java 1 SQL 1 Excel 2 Oracle 2 SQL SQL> create or replace function f (i number) return varchar2 as 2 o varchar2(20); 3 begin 4 for r in (select skill into o from b where id = i) loop 5 o := o || ',' || r.skill; 6 end loop; 7 o := substr(o, 2); 8 return o; 9 end; 10 / Function created. SQL> select id, name, f(id) skill from a; ID NAME SKILL ---------- ---------- -------------------- 1 David Java,SQL,Excel 2 Yong Oracle,SQL Or if you don't need functionality more than connecting multiple items into a string, you can even use an Oracle's built-in function, wmsys.wm_concat (10g and up): SQL> col skill for a20 SQL> select a.id, name, wmsys.wm_concat(skill) skill from a, b where a.id = b.id group by a.id, name; ID NAME SKILL ------------ ---------- -------------------- 1 David Java,SQL,Excel 2 Yong Oracle,SQL But of course anything undocumented is discouraged. For this particular function, Oracle even has a note of warning: WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function [ID 1336219.1] Since 11gR2, Oracle's function listagg should be used: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm http://www.oracle.com/technetwork/issue-archive/2013/13-jul/o43sql-1958802.html [2010-10 update] If you need each skill in its own column instead of concatenated into a string: SQL> with x as (select row_number() over (partition by id order by id) rn, b.* from b) 2 select a.*, 3 (select skill from x where a.id=x.id and x.rn=1) skill1, 4 (select skill from x where a.id=x.id and x.rn=2) skill2, 5 (select skill from x where a.id=x.id and x.rn=3) skill3 6 from a; ID NAME SKILL1 SKILL2 SKILL3 ------------ ---------- ---------- ---------- ---------- 1 David Java SQL Excel 2 Yong Oracle SQL