Home » SQL & PL/SQL » SQL & PL/SQL » pivot query
pivot query [message #447594] Tue, 16 March 2010 05:34 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi All,

I tried to conver rows to columns using the pivot method.But i am not able to do that.

This is what i tried:

SQL> desc pt
 Name              Null?    Type
 ----------------- -------- ------------
 NAME                       VARCHAR2(10)
 YEAR                       NUMBER(4)
 VALUE                      NUMBER(4)

SQL> select * from pt;

NAME             YEAR      VALUE
---------- ---------- ----------
john             1991       1000
john             1992       2000
john             1993       3000
jack             1991       1500
jack             1992       1200
jack             1993       1340
mary             1991       1250
mary             1992       2323
mary             1993       8700

9 rows selected.

SQL> select year , max (decode ( name,'john',value,0)) "JOHN",
  2                max (decode ( name,'jack',value,0)) "JACK",
  3                max (decode ( name,'mary',value,0)) "MARY"
  4  from pt
  5  group by year;

      YEAR       JOHN       JACK       MARY
---------- ---------- ---------- ----------
      1993          0          0          0
      1991       1000          0          0
      1992          0          0          0


As you can see from the above output i am not getting as expected.

Expected output:

      YEAR       JOHN       JACK       MARY
---------- ---------- ---------- ----------
      1993       3000       1340       8700 
      1991       1000       1500       1250
      1992       2000       1200       2323
Re: pivot query [message #447597 is a reply to message #447594] Tue, 16 March 2010 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel
Re: pivot query [message #447598 is a reply to message #447594] Tue, 16 March 2010 05:44 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your query is correct (sort of), but data is not. I bet that there are space characters along with "names", everywhere except for the "john + 1991 + 1000" combination.

Something like
"john "
instead of
"john"


Try to TRIM the NAME column.
Re: pivot query [message #447600 is a reply to message #447594] Tue, 16 March 2010 05:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It works just fine for me:
create table test_148 (name                       varchar2(10)
                      ,year                       number(4)
                      ,value                      number(4));

insert into test_148 values('john',             1991,       1000);
insert into test_148 values('john',             1992,       2000);
insert into test_148 values('john',             1993,       3000);
insert into test_148 values('jack',             1991,       1500);
insert into test_148 values('jack',             1992,       1200);
insert into test_148 values('jack',             1993,       1340);
insert into test_148 values('mary',             1991,       1250);
insert into test_148 values('mary',             1992,       2323);
insert into test_148 values('mary',             1993,       8700);

select year , max (decode ( name,'john',value,0)) "JOHN",
              max (decode ( name,'jack',value,0)) "JACK",
              max (decode ( name,'mary',value,0)) "MARY"
from test_148
group by year;

      YEAR       JOHN       JACK       MARY
---------- ---------- ---------- ----------
      1993       3000       1340       8700
      1991       1000       1500       1250
      1992       2000       1200       2323


I'd check to see that you've actually got the data you think you have in the table.
Re: pivot query [message #447603 is a reply to message #447600] Tue, 16 March 2010 06:00 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Thanks all,

I end up in adding space along with the names , that caused the trouble.

Cheers
Ravi
Re: pivot query [message #447605 is a reply to message #447603] Tue, 16 March 2010 06:01 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hehe!
Re: pivot query [message #447718 is a reply to message #447594] Wed, 17 March 2010 09:11 Go to previous messageGo to next message
tejas.patel
Messages: 22
Registered: December 2008
Location: NJ
Junior Member

dear

select year , max (decode ( name,'john',value,0)) "JOHN",
max (decode ( name,'jack',value,0)) "JACK",
max (decode ( name,'mary',value,0)) "MARY"
from pt
group by year;


you are check query. this is a right query.
Re: pivot query [message #447720 is a reply to message #447718] Wed, 17 March 2010 09:28 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course this is the right query, this is the one that JRowbottom gave yesterday!
I note that NONE of your posts are useful.

Regards
Michel
Previous Topic: DETECT AND MOVE DUPLICATE VALUES (2 threads merged by bb)
Next Topic: indexes for query performance
Goto Forum:
  


Current Time: Mon Sep 26 19:39:07 CDT 2016

Total time taken to generate the page: 0.07488 seconds