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
> <[email protected]<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
>> <[email protected]<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
>>> <[email protected]<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
>>>> [email protected]<javascript:>
>>>> To unsubscribe from this group, send email to
>>>> [email protected] <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 [email protected] <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 [email protected]<javascript:>
>> To unsubscribe from this group, send email to
>> [email protected] <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 [email protected] <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 [email protected]
To unsubscribe from this group, send email to
[email protected]
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
