Home » SQL & PL/SQL » SQL & PL/SQL » Help in Pivot Query... (Oracle 10.2.0.1)
Help in Pivot Query... [message #387418] Thu, 19 February 2009 03:19 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

First of all let me tell you that i have searched for "PIVOT", and i believe that i didn't get the desired results from there, thats the reason i am posting it here..please help me.

I have a table ,

CREATE TABLE TESTSS
(
  CUST      NUMBER,
  ID        NUMBER,
  TAX_CODE  NUMBER,
  CITY      VARCHAR2(20 BYTE)
);


Insert Script...

INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES ( 
123, 2, 222, 'Bangalore'); 
INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES ( 
123, 3, 333, 'Delhi'); 
INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES ( 
123, 4, 444, 'Mysore'); 
INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES ( 
124, 2, 222, 'Bangalore'); 
INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES ( 
124, 3, 333, 'Mysore'); 
INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES ( 
125, 4, 444, 'Mysore'); 
COMMIT;


I need the following output

Cust,City1,Tax_Code1,City2,Tax_Code2,City3,Tax_Code3
123,Bangalore,222,Delhi,333,Mysore,444
124,Bangalore,222,Mysore,333
125,Mysore,444

Please help me..

Regards,
Ashoka BL
Re: Help in Pivot Query... [message #387424 is a reply to message #387418] Thu, 19 February 2009 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i believe that i didn't get the desired results from there

Post what you tried. I think some of the pivot queries that are in the different topics answer you question.
(And format your result.)

Regards
Michel
Re: Help in Pivot Query... [message #387435 is a reply to message #387418] Thu, 19 February 2009 04:10 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi Michel,

I tried and i didnt get the proper results,

These are the queries which i tried ( which although gave me wrong results)

SELECT cust, city1, code1, city2, code2
  FROM (SELECT cust, DECODE (ROWNUM, 1, city, NULL) city1,
               DECODE (ROWNUM, 1, tax_code, NULL) code1,
               DECODE (ROWNUM, 2, city, NULL) city2,
               DECODE (ROWNUM, 2, tax_code, NULL) code2
          FROM testss)


SELECT a.cust, a.city1, a.tax1, b.city1, b.tax1
  FROM (SELECT ROWNUM r, cust, DECODE (ROWNUM, 1, city, NULL) city1,
               DECODE (ROWNUM, 1, tax_code, NULL) tax1
          FROM testss ) a 
		  left outer join 
       (SELECT ROWNUM r, cust, DECODE (ROWNUM, 2, city, NULL) city1,
               DECODE (ROWNUM, 2, tax_code, NULL) tax1
          FROM testss ) b  on a.r=b.r 


SELECT   cust, MAX (city1) City1, MAX (code1) Code1, MAX (city2) City2, MAX (code2) Code2
    FROM (SELECT cust, DECODE (ROWNUM, 1, city, NULL) city1,
                 DECODE (ROWNUM, 1, tax_code, NULL) code1,
                 DECODE (ROWNUM, 2, city, NULL) city2,
                 DECODE (ROWNUM, 2, tax_code, NULL) code2
            FROM testss)
GROUP BY cust



I also looked into Kevin's Blog http://www.orafaq.com/node/1871

I was out of options and that's reason i had asked the question.

Regards,
Ashoka BL
Re: Help in Pivot Query... [message #387439 is a reply to message #387418] Thu, 19 February 2009 04:16 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Sorry to mention..

This is the desired output..

Cust	City1	       Code1	City2	Code2	City3	Code3

123	Bangalore	222	Delhi	333	Mysore	444
124	Mysore	        444				



Regards,
Ashoka BL
Re: Help in Pivot Query... [message #387442 is a reply to message #387418] Thu, 19 February 2009 04:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ROWNUM will not give you desired output.

Instead, Form an inline view with analytic function and try query the same queries on it .

Smile
Rajuvan



Re: Help in Pivot Query... [message #387443 is a reply to message #387418] Thu, 19 February 2009 04:28 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey Raju,

Thanks for the quick post...

I would appreciate if you could provide me some sample script to do this, i know analytical functions, but which one to use ?

Regards,
Ashoka BL
Re: Help in Pivot Query... [message #387447 is a reply to message #387443] Thu, 19 February 2009 04:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
ROW_NUMBER or DENSE_RANK will both do the job.

Re: Help in Pivot Query... [message #387448 is a reply to message #387418] Thu, 19 February 2009 04:44 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey Raju,

this is the query which i wrote now...

 select cust,Max(decode(rows1,1 , city)) City1,
 			Max(decode(rows1,1 , tax_code)) code1,
 			Max(decode(rows1,2 , city)) City2,
			Max(decode(rows1,2 , tax_code)) code1,
			Max(decode(rows1,3 , city)) City3,
			Max(decode(rows1,3 , tax_code)) code1 
from (select cust,tax_code,city,
             row_number() over (partition by cust order by cust) rows1 
      from testss)
group by cust;


Think i got the correct results, pelase verify once...

Regards,
Ashoka BL

[Updated on: Thu, 19 February 2009 04:48] by Moderator

Report message to a moderator

Re: Help in Pivot Query... [message #387451 is a reply to message #387448] Thu, 19 February 2009 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is correct.
If you want a specific order in the column values review your order by clause.

Regards
Michel
Re: Help in Pivot Query... [message #387455 is a reply to message #387418] Thu, 19 February 2009 05:06 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey Michel,

Thank you very much, i really tried hard for this, although i did many PIVOT queries,but the end result is fruitful.

Really thankful for the replies

Regards,
Ashoka BL
Re: Help in Pivot Query... [message #387576 is a reply to message #387418] Thu, 19 February 2009 23:12 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I would like to add more requirement for this,

I would able to get the query as following,

SELECT   cust, MAX (DECODE (rows1, 1, city)) city1,
         MAX (DECODE (rows1, 1, tax_code)) code1,
         MAX (DECODE (rows1, 2, city)) city2,
         MAX (DECODE (rows1, 2, tax_code)) code2,
         MAX (DECODE (rows1, 3, city)) city3,
         MAX (DECODE (rows1, 3, tax_code)) code3
    FROM (SELECT cust, tax_code, city,
                 Row_Number () OVER (PARTITION BY cust order by cust desc) rows1
            FROM testss)
GROUP BY cust


Assume i want the columns dynamically..how do i do it ?

Now i am hardcoding the rownum assuming that i have only 3 cities and 3 tax_codes, if i dont know how many cities are there ,but still i need the columns to be generated automatically,how do i do it ?

Example OutPut
CUST	CITY1	CODE1	CITY2	CODE2	CITY3	CODE3	City4	Code4	City5	Code5.....
123	Bangalore	222	Delhi	444	Mysore	333				
124	Bangalore	222								
125	Delhi	444								

Re: Help in Pivot Query... [message #387583 is a reply to message #387576] Thu, 19 February 2009 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is much more difficult and basically you can't in pure SQL but you can workaround this.
I had posted several ways to do it on AskTom several years ago, search for "pivot" topics here I gave several links.

Regards
Michel
Re: Help in Pivot Query... [message #387586 is a reply to message #387418] Thu, 19 February 2009 23:38 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Thanks Michel,

Appreciate your quick answer, although its not a requirement, i was just curious of how do we do if we face this scenario !

Well, i will dfntly Search for "PIVOT", and will try to get any close scenario..

But if its not possible in single SQL,then do we have to create a PL/SQL block ? if so, lemme try that as well.

Regards,
Ashoka BL
Re: Help in Pivot Query... [message #387606 is a reply to message #387586] Fri, 20 February 2009 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But if its not possible in single SQL,then do we have to create a PL/SQL block ?

It may one of the workaround but you either have no more real columns (but a string looking like columns) and use dbms_output, or have to create type and pipelined function.

Regards
Michel
Re: Help in Pivot Query... [message #387872 is a reply to message #387586] Sat, 21 February 2009 02:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE TESTSS
  2  (
  3    CUST	 NUMBER,
  4    ID	 NUMBER,
  5    TAX_CODE  NUMBER,
  6    CITY	 VARCHAR2(9)
  7  );

Table created.

SCOTT@orcl_11g> BEGIN
  2    INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
  3    123, 2, 222, 'Bangalore');
  4    INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
  5    123, 3, 333, 'Delhi');
  6    INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
  7    123, 4, 444, 'Mysore');
  8    INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
  9    124, 2, 222, 'Bangalore');
 10    INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
 11    124, 3, 333, 'Mysore');
 12    INSERT INTO TESTSS ( CUST, ID, TAX_CODE, CITY ) VALUES (
 13    125, 4, 444, 'Mysore');
 14    COMMIT;
 15  END;
 16  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET AUTOPRINT ON
SCOTT@orcl_11g> VARIABLE g_results REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_cities  NUMBER;
  3    v_sql	 VARCHAR2 (32767);
  4  BEGIN
  5    SELECT MAX (COUNT (city)) INTO v_cities FROM testss GROUP BY cust;
  6    v_sql := 'SELECT cust';
  7    FOR i IN 1 .. v_cities LOOP
  8  	 v_sql := v_sql || ', MAX (DECODE (rows1, ' || i || ', city))	  city' || i;
  9  	 v_sql := v_sql || ', MAX (DECODE (rows1, ' || i || ', tax_code)) code' || i;
 10    END LOOP;
 11    v_sql := v_sql ||
 12  	 ' FROM  (SELECT cust, tax_code, city,
 13  			 ROW_NUMBER () OVER (PARTITION BY cust ORDER BY city) rows1
 14  		  FROM	 testss)
 15  	   GROUP BY cust';
 16    OPEN :g_results FOR v_sql;
 17  END;
 18  /

PL/SQL procedure successfully completed.


      CUST CITY1          CODE1 CITY2          CODE2 CITY3          CODE3
---------- --------- ---------- --------- ---------- --------- ----------
       123 Bangalore        222 Delhi            333 Mysore           444
       124 Bangalore        222 Mysore           333
       125 Mysore           444

SCOTT@orcl_11g> 

Re: Help in Pivot Query... [message #387997 is a reply to message #387418] Mon, 23 February 2009 00:07 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Barbara,

Thank you very much, even though it was not requirement, i was just curious about how to do this, u made it very simple !!!

Cool..

Regards,
Ashoka BL
Bengaluru
Re: Help in Pivot Query... [message #388032 is a reply to message #387997] Mon, 23 February 2009 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This nice example from Barbara hides the most difficult part done by SQL*Plus: getting the column names and values from a cursor.

Have a look at T. Kyte print_table procedure to see an example of how to do it.

Regards
Michel
Re: Help in Pivot Query... [message #388037 is a reply to message #387418] Mon, 23 February 2009 01:04 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Michel,

Sure i will look at it, Thanks

Regards,
Ashoka BL
Bengaluru
icon14.gif  Re: Help in Pivot Query... [message #388073 is a reply to message #387872] Mon, 23 February 2009 02:08 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
@Barbara, thank You for showing and sharing this (for me) new and amazing method.
Previous Topic: Pivot Table
Next Topic: Invalid zipped file after using UTL_FILE and UTL_SMTP
Goto Forum:
  


Current Time: Sat Dec 10 22:33:42 CST 2016

Total time taken to generate the page: 0.11302 seconds