Home » SQL & PL/SQL » SQL & PL/SQL » Pivot Table (9.2.0.7)
Pivot Table [message #387869] |
Sat, 21 February 2009 01:55  |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
I have data as following
SQL> select col1, col2,col3, col4, col5 from R1 ;
COL1 COL2 COL3 COL4 COL5
--------- ----- ----- ----- ---------
31-DEC-08 A 100
31-DEC-08 B C 50
31-DEC-08 A B 200
31-DEC-08 A B C 500
I want to Pivot/transform the data as following
SQL> ed
Wrote file afiedt.buf
line 6 truncated.
1 select col1, col2 COL_123, sum(col5) from R1 Where col2 = 'A' group by col1, col2
2 UNION
3 select col1, col3 COL_123, sum(col5) from R1 Where col3 = 'B' group by col1, col3
4 UNION
5* select col1, col4 COL_123, sum(col5) from R1 Where col4 = 'C' group by col1, col4
6 /
COL1 COL_1 SUM(COL5)
--------- ----- ---------
31-DEC-08 A 800
31-DEC-08 B 750
31-DEC-08 C 550
SQL>
Using Pivot, I could not get data in single column as mentioned above. Using above query I need to select table multiple times.
Is there a better way (Using single select)
Brian
|
|
|
|
Re: Pivot Table [message #387880 is a reply to message #387870] |
Sat, 21 February 2009 04:21   |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
create table R1 (col1 Date, col2 char(1),col3 char(1),col4 char(1),col5 number(5));
Insert into R1 values('31-Dec-2008','A',null,null,100);
Insert into R1 values('31-Dec-2008',null,'B','C',50);
Insert into R1 values('31-Dec-2008','A','B',null,200);
Insert into R1 values('31-Dec-2008','A','B','C',500);
SQL> select col1, col2,col3, col4, col5 from R1 ;
COL1 COL2 COL3 COL4 COL5
--------- ----- ----- ----- ---------
31-DEC-08 A 100
31-DEC-08 B C 50
31-DEC-08 A B 200
31-DEC-08 A B C 500
I'm expecting following result using single query
1 select col1, col2 COL_123, sum(col5) from R1 Where col2 = 'A' group by col1, col2
2 UNION
3 select col1, col3 COL_123, sum(col5) from R1 Where col3 = 'B' group by col1, col3
4 UNION
5* select col1, col4 COL_123, sum(col5) from R1 Where col4 = 'C' group by col1, col4
6 /
COL1 COL_1 SUM(COL5)
--------- ----- ---------
31-DEC-08 A 800
31-DEC-08 B 750
31-DEC-08 C 550
Brian
|
|
|
Re: Pivot Table [message #387893 is a reply to message #387880] |
Sat, 21 February 2009 09:45   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> create table R1 (col1 VARCHAR2(20), col2 char(1),col3 char(1),col4 char(1),col5 number(5));
SQL> Insert into R1 values('31-Dec-2008','A',null,null,100);
SQL> Insert into R1 values('31-Dec-2008',null,'B','C',50);
SQL> Insert into R1 values('31-Dec-2008','A','B',null,200);
SQL> Insert into R1 values('31-Dec-2008','A','B','C',500);
SQL> commit;
SQL> with
2 col as ( select level col from dual connect by level <= 3 ),
3 data as (
4 select col1, decode(col, 1,col2, 2,col3, 3,col4) col_123, col, col5
5 from r1, col
6 )
7 select col1, col_123, sum(col5)
8 from data
9 where (col,col_123) in ((1,'A'), (2,'B'), (3,'C'))
10 group by col1, col_123
11 order by col1, col_123
12 /
COL1 C SUM(COL5)
-------------------- - ----------
31-Dec-2008 A 800
31-Dec-2008 B 750
31-Dec-2008 C 550
3 rows selected.
But if you have to do this then your design is wrong.
Regards
Michel
[Updated on: Sat, 21 February 2009 09:46] Report message to a moderator
|
|
|
Re: Pivot Table [message #388059 is a reply to message #387893] |
Mon, 23 February 2009 01:33   |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Thanks Michel,
It is not a design issue, basically for while reporting one record qualifies for one or multiple categories(in my case A/B/C). So I could give as columns , but i didnt know how to represent the way mentioned above.
Anyway thanks very much and try to implement the same.
Brian
|
|
|
Re: Pivot Table [message #388069 is a reply to message #388059] |
Mon, 23 February 2009 01:54  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | It is not a design issue,
|
I disagree with you. If you have to do this, then you should have a "type" column (1, 2 or 3) and a "value in the type" one ('A','B' or 'C').
This is what my "data" subquery simulates.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Thu Feb 06 12:24:27 CST 2025
|