On Friday, April 19, 2013 4:36:50 AM UTC-6, Akshay T wrote: > > Hi Gerffson, > In my opinion it could be done by following logic:- > > > Select Name from* table_name* > Union all > Select Quantity from *table_name* > > > On Fri, Apr 12, 2013 at 11:17 PM, Michael Moore > <michae...@gmail.com<javascript:> > > wrote: > >> Actually I like this one much better except for the fact that the author >> uses "count (1)" in his example; a practice that I deplore. >> http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html >> >> >> >> On Fri, Apr 12, 2013 at 10:39 AM, Michael Moore >> <michae...@gmail.com<javascript:> >> > wrote: >> >>> What you want is called a pivot query. If you google "oracle sql pivot" >>> you will get a list of resources to explain this. Here is one of them. >>> http://www.dba-oracle.com/t_pivot_examples.htm >>> >>> >>> >>> On Fri, Apr 12, 2013 at 9:42 AM, Gerffson Junior >>> <gerf...@gmail.com<javascript:> >>> > wrote: >>> >>>> >>>> Help me please ! >>>> >>>> I have a table with two columns. >>>> >>>> NAME QUANTITY >>>> ---------- ---------- >>>> COL_A 5000 >>>> COL_B 3000 >>>> COL_C 3000 >>>> >>>> I want to transform the rows into columns. >>>> >>>> Below, thats the result that I need >>>> >>>> COL_A COL_B COL_C >>>> --------- ---------- ----------- >>>> 3000 3000 5000 >>>> >>>> If the table have 20 rows, the result must have 20 columns >>>> >>>> Thanks ! >>>> >>>> -- >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "Oracle PL/SQL" group. >>>> To post to this group, send email to >>>> oracle...@googlegroups.com<javascript:> >>>> To unsubscribe from this group, send email to >>>> oracle-plsql...@googlegroups.com <javascript:> >>>> For more options, visit this group at >>>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>>> >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "Oracle PL/SQL" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to oracle-plsql...@googlegroups.com <javascript:>. >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>>> >>>> >>> >>> >> -- >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to oracle...@googlegroups.com<javascript:> >> To unsubscribe from this group, send email to >> oracle-plsql...@googlegroups.com <javascript:> >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> >> --- >> You received this message because you are subscribed to the Google Groups >> "Oracle PL/SQL" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to oracle-plsql...@googlegroups.com <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > > > > -- > Regards, > Akshay S. Tanksale > Mobile No:-9833382515 >
No, it can't, that produces this: SQL> Select Name from name_qty 2 Union all 3 Select Quantity from name_qty 4 / Select Name from name_qty * ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression because the data types do not match. Of course we can change the query a bit and still not get what you think you'll get: SQL> Select Name from name_qty 2 Union all 3 Select to_char(Quantity) from name_qty 4 / NAME ---------------------------------------- COL_A COL_B COL_C 5000 3000 3000 6 rows selected. SQL> There is no translation from rows to columns with your example. Let's try a real pivot query and see what we get: SQL> column namelist new_value n_list noprint SQL> SQL> select wm_concat(''''||name||'''') namelist 2 from name_qty 3 connect by nocycle name = prior name 4 group by level; SQL> SQL> select * 2 from 3 (select name, quantity 4 from name_qty nq 5 ) 6 pivot 7 ( 8 sum(quantity) 9 for name in (&n_list) 10 ); 'COL_A' 'COL_C' 'COL_B' ---------- ---------- ---------- 5000 3000 3000 SQL> SQL> insert all 2 into name_qty 3 values('COL_D', 5000) 4 into name_qty 5 values('COL_E', 3000) 6 into name_qty 7 values('COL_F', 3000) 8 select * From dual; 3 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> select wm_concat(''''||name||'''') namelist 2 from name_qty 3 connect by nocycle name = prior name 4 group by level; SQL> SQL> select * 2 from 3 (select name, quantity 4 from name_qty nq 5 ) 6 pivot 7 ( 8 sum(quantity) 9 for name in (&n_list) 10 ); 'COL_A' 'COL_F' 'COL_E' 'COL_D' 'COL_C' 'COL_B' ---------- ---------- ---------- ---------- ---------- ---------- 5000 3000 3000 5000 3000 3000 SQL> Now we have translation. A UNION simply won't work. David Fitzjarrell -- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en --- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.