Home » SQL & PL/SQL » SQL & PL/SQL » Pivot Table (9.2.0.7)
Pivot Table [message #387869] Sat, 21 February 2009 01:55 Go to next message
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 #387870 is a reply to message #387869] Sat, 21 February 2009 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Pivot Table [message #387880 is a reply to message #387870] Sat, 21 February 2009 04:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: how to show dynamic column aliases
Next Topic: Help in Pivot Query...
Goto Forum:
  


Current Time: Thu Dec 08 12:46:12 CST 2016

Total time taken to generate the page: 0.12538 seconds