Home » SQL & PL/SQL » SQL & PL/SQL » Simple Table Manipluation
Simple Table Manipluation Tue, 20 November 2007 10:56
 Vackar Messages: 81Registered: 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

Vackar
Re: Simple Table Manipluation [message #282048 is a reply to message #282047] Tue, 20 November 2007 11:38
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Frank Naude Messages: 4516Registered: 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
 Vackar Messages: 81Registered: October 2007 Member
Thanks, that was just what I needed.
 Previous Topic: want to update a column of complex table Next Topic: Dynamic SQL
Goto Forum:

Current Time: Sun Aug 20 12:31:04 CDT 2017

Total time taken to generate the page: 0.21200 seconds