Home » SQL & PL/SQL » SQL & PL/SQL » Simple Table Manipluation
Simple Table Manipluation [message #282047] Tue, 20 November 2007 10:56 Go to next message
Vackar
Messages: 81
Registered: October 2007
Member
Hi,

Does anyone know how to turn this:


-------------------------------------------------------------------------------------------
NumberValue1                          StringValue1         StringValue2
-------------------------------------------------------------------------------------------
         5                               a                          p1
         8                               b                          p2
         9                               c                          p3
         1                               d                          p2
         4                               e                          p2
         3                               f                          p1
         2                               g                          p2
-------------------------------------------------------------------------------------------



into this:



-------------------------------------------------------------------------------------------
StringValue1              P1        P2         P3  
-------------------------------------------------------------------------------------------
        a                  5          0          0
        b                  0          8          0
        c                  0          0          9
        d                  0          1          0
        e                  0          4          0
        f                  3          0          0
        g                  0          0          0
-------------------------------------------------------------------------------------------




where the numbers in between are the NumberVal1 from table1




Thanks in advance,
Vackar
Re: Simple Table Manipluation [message #282048 is a reply to message #282047] Tue, 20 November 2007 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All those that read this forum know how to do this.
This is called "pivot" and has been asked many times;
Just search for the solution.

Regards
Michel
Re: Simple Table Manipluation [message #282049 is a reply to message #282047] Tue, 20 November 2007 11:40 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Something like this should do:

SELECT StringValue1,
       sum(decode(StringValue2, 'p1', NumberValue1, 0)) "P1",
       sum(decode(StringValue2, 'p2', NumberValue1, 0)) "P2",
       sum(decode(StringValue2, 'p3', NumberValue1, 0)) "P3"
  FROM test
 GROUP BY StringValue1
/


PS: This is a FAQ. See http://www.orafaq.com/faq/how_does_one_code_a_matrix_crosstab_pivot_report_in_sql
Re: Simple Table Manipluation [message #282245 is a reply to message #282049] Wed, 21 November 2007 04:21 Go to previous message
Vackar
Messages: 81
Registered: October 2007
Member
Thanks, that was just what I needed. Cool
Previous Topic: Need Insert Script dynamically
Next Topic: error during inserting(ORA-01403: no data found)
Goto Forum:
  


Current Time: Sat Dec 10 16:21:58 CST 2016

Total time taken to generate the page: 0.12234 seconds