Home » SQL & PL/SQL » SQL & PL/SQL » PIVOT query (Any)
PIVOT query [message #417483] Sun, 09 August 2009 18:11 Go to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Please view attached doc and let me know if it is possible? Thanks.
Re: PIVOT query [message #417485 is a reply to message #417483] Sun, 09 August 2009 19:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDCEJJE

http://www.orafaq.com/forum/?SQ=f85560360203e3cf9e66c714b6f75d7d&t=search&srch=pivot&btn_submit=Search&field=subject& amp;forum_limiter=1&search_logic=AND&sort_order=DESC&author=

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Sun, 09 August 2009 22:06]

Report message to a moderator

Re: PIVOT query [message #417517 is a reply to message #417483] Mon, 10 August 2009 00:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ramesh_samane wrote on Mon, 10 August 2009 01:11
Please view attached doc and let me know if it is possible? Thanks.


If everyone were to describe their problems in attachments, there would be no possibility to search the forum for problems answered previously, so next time please describe your problem in the post and not in an attachment
Re: PIVOT query [message #417542 is a reply to message #417517] Mon, 10 August 2009 02:43 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
DROP TABLE normalized_data;

CREATE TABLE normalized_data
(product_id NUMBER,
file_id NUMBER,
file_name VARCHAR2(15),
file_date DATE);

BEGIN
   INSERT INTO normalized_data
        VALUES (50, 1, 'bob.jpg', TO_DATE ('16-MAY-2008', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 2, 'richard.jpg', TO_DATE ('02-APR-2007', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 3, 'orion.jpg', TO_DATE ('25-OCT-2006', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 4, 'blah.jpg', TO_DATE ('09-JAN-2010', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 5, 'proposal.jpg',
                TO_DATE ('06-DEC-2027', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 6, 'sirenl.jpg', TO_DATE ('18-DEC-1982', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (75, 1, 'asdf.jpg', TO_DATE ('27-AUG-2027', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (75, 5, '2asdf.jpg', TO_DATE ('10-APR-2038', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (75, 6, 'k4.jpg', TO_DATE ('11-APR-2038', 'DD-MON-YYYY'));
END;

COMMIT

SELECT *
  FROM normalized_data;
 
SELECT DISTINCT product_id,
                MAX (DECODE (x.file_id, 1, x.file_name)) file_name1,
                MAX (DECODE (x.file_id, 1, x.file_date)) file_date1,
                MAX (DECODE (x.file_id, 2, x.file_name)) file_name2,
                MAX (DECODE (x.file_id, 2, x.file_date)) file_date2,
                MAX (DECODE (x.file_id, 3, x.file_name)) file_name3,
                MAX (DECODE (x.file_id, 3, x.file_date)) file_date3,
                MAX (DECODE (x.file_id, 4, x.file_name)) file_name4,
                MAX (DECODE (x.file_id, 4, x.file_date)) file_date4,
                MAX (DECODE (x.file_id, 5, x.file_name)) file_name5,
                MAX (DECODE (x.file_id, 5, x.file_date)) file_date5,
                MAX (DECODE (x.file_id, 6, x.file_name)) file_name6,
                MAX (DECODE (x.file_id, 6, x.file_date)) file_date6
           FROM (SELECT   *
                     FROM normalized_data
                 ORDER BY product_id, file_id) x
       GROUP BY product_id

this works if you have 6 columns. how can we make it dynamic?
Re: PIVOT query [message #417601 is a reply to message #417483] Mon, 10 August 2009 07:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
If you are using 11g, then you can use PIVOT XML clause to make it dynamic.
See example here.
No any other way.

regards,
Delna
Re: PIVOT query [message #417610 is a reply to message #417601] Mon, 10 August 2009 08:18 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PIVOT XML returns an XML string which is far from a dynamic number of columns.

Regards
Michel
Previous Topic: To capture the Number of rows in each partition of a table
Next Topic: how to gather statistics in oracle 9i
Goto Forum:
  


Current Time: Sun Dec 11 04:10:23 CST 2016

Total time taken to generate the page: 0.04408 seconds