Home » SQL & PL/SQL » SQL & PL/SQL » help with query
help with query [message #204643] Tue, 21 November 2006 08:37 Go to next message
SITO
Messages: 19
Registered: March 2005
Location: SWE
Junior Member
Hi,

sorry for the title..But i couldn't come up with a title which describes what i wanna do

Here's the table....(in a 8g db)


    GRP_NO AREA    HITS
---------- ---- -------
   1517497 45         1
   1517498 40        31
   1517498 43         1
   1517498 45         3
   1517498 50         1
   1517555 40         2
   1517567 40         1
   1517580 40         1


Based on this i want to show the hitspread between areas within the grp_no.
So the desired output is..

     GRP_NO    40  43  45  50
-----------   --- --- --- ---
1517497                 1
1517498        31   1   3   1
1517555         2
1517567         1
1517580         1





Any good ideas ?

brgs
Re: help with query [message #204652 is a reply to message #204643] Tue, 21 November 2006 09:13 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Not in the desired format, but here is how you can get the data.

select grp_no,area,min(hits) as hits from table_name
group by grp_no,area
order by grp_no,area


GRP_NO AREA HITS
1 497 45 1
2 498 40 31
3 498 43 1
4 498 45 3
5 498 50 1
6 555 40 2
7 567 40 1
8 580 40 1

Hope this helps.

Marc

[Updated on: Tue, 21 November 2006 09:14]

Report message to a moderator

Re: help with query [message #204653 is a reply to message #204652] Tue, 21 November 2006 09:30 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
You can use decode to get the format you want:
SELECT yt.grp_no
      ,SUM(decode(yt.area
                 ,40
                 ,1
                 ,0)) area_40
      ,SUM(decode(yt.area
                 ,43
                 ,1
                 ,0)) area_43
      ,SUM(decode(yt.area
                 ,45
                 ,1
                 ,0)) area_45
      ,SUM(decode(yt.area
                 ,50
                 ,1
                 ,0)) area_40
FROM   your_table yt
GROUP  BY yt.grp_no
Re: help with query [message #204655 is a reply to message #204643] Tue, 21 November 2006 09:34 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Except not very practical if there are hundreds of areas :}
Re: help with query [message #204662 is a reply to message #204655] Tue, 21 November 2006 10:07 Go to previous messageGo to next message
SITO
Messages: 19
Registered: March 2005
Location: SWE
Junior Member
fortunately there isn't that many areas so the decode-solution will work..

Thanks all for the help !
Re: help with query [message #204663 is a reply to message #204662] Tue, 21 November 2006 10:15 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
If you want, you can have your view definition generated (saves you some typo's maybe Wink):

SQL> DECLARE
  2    v_columns   VARCHAR2(32000) DEFAULT NULL;
  3    v_statement VARCHAR2(32000) DEFAULT NULL;
  4  BEGIN
  6    FOR i IN (SELECT DISTINCT yt.area
  7              FROM   your_table yt)
  8    LOOP
  9      v_columns := v_columns || ', sum(decode(yt.area, ' || i.area ||
 10                   ', 1,0)) AS "' || i.area || '"';
 12    END LOOP;
 13    v_statement := 'create or replace view your_pivot as select yt.grp_no ' ||
 14                   v_columns || ' from your_table yt group by yt.grp_no';
 15    execute immediate v_statement;
 16  END;
 17  /

PL/SQL procedure successfully completed

SQL> select * from your_pivot;

    GRP_NO         40         43         45         50
---------- ---------- ---------- ---------- ----------
   1517497          0          0          1          0
   1517498          1          1          1          1
   1517555          1          0          0          0
   1517567          1          0          0          0
   1517580          1          0          0          0



(edit: removed some debugging)

[Updated on: Tue, 21 November 2006 10:16]

Report message to a moderator

Previous Topic: how to create dynamic table
Next Topic: Oracle Keywords
Goto Forum:
  


Current Time: Thu Dec 08 18:39:13 CST 2016

Total time taken to generate the page: 0.23687 seconds