Home » SQL & PL/SQL » SQL & PL/SQL » Please help me in Dynamic Row to Column in Oracle 8i
Please help me in Dynamic Row to Column in Oracle 8i [message #227402] Wed, 28 March 2007 03:06 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I know to use the pivot by knowing the row value and converting into column name but since I don't know what is row value I want to display all the rows into column and below each column the amount to be displayed.

My SQL is like this

select a.CONT_NO,a.CONT_DATE,b.DESCRIPTION,b.COMM_AMT
from css_cont_hdr a, css_cont_special_comm b
where a.CFM_DATE between '01-JAN-07' AND '01-FEB-07'
and a.REF_NO=b.CTHD_REF_NO
ORDER BY 1;

My current output like this

CONT_NO	   CONT_DATE DESCRIPTION	COMM_AMT
TCTU602388 26-Dec-06 ACTUAL COMM	500
TCTU602388 26-Dec-06 SPOTTER'S COMM	1250
TCTU602388 26-Dec-06 OVERTRADE COMM	3000
TCTU602389 26-Dec-06 ACTUAL COMM	500
TCTU602389 26-Dec-06 OVERTRADE COMM	3000
TCTU602389 26-Dec-06 SPOTTER'S COMM	1250
TCTU700096 12-Jan-07 ACTUAL COMM	500
TCTU700096 12-Jan-07 TAILGATE COMM	738
TCTU700096 12-Jan-07 SPOTTER'S COMM	900
TCTU700102 13-Jan-07 ACTUAL COMM	750
TCTU700102 13-Jan-07 DEALER COMM - EK AUTO (EXPORT) PTE LTD	900
TCTU700102 13-Jan-07 BOX COMM	500
TCTU700153 16-Jan-07 ACTUAL COMM	750
TCTU700153 16-Jan-07 DEALER COMM (KRAFTWAGEN)	1450
TCTU700153 16-Jan-07 CANOPY COMM	150


I want to description values to be converted into column and the comm_amt below the description the description will not be same it may vary you can see the above example.

Like

Cont_no    cont_date  ACTUAL COMM SPOTTER'S COMM OVERTRADE COMM
TCTU602388 26-Dec-06   	     500           1250           3000


Can any expert help me in this regard?

[Updated on: Wed, 28 March 2007 03:29] by Moderator

Report message to a moderator

Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227418 is a reply to message #227402] Wed, 28 March 2007 03:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My script:
WITH yourtable AS
(
  SELECT 'TCTU602388'                         cont_no
       , TO_DATE('26-Dec-2006','dd-Mon-yyyy') cont_date
       , 'ACTUAL'                             description
       , 500                                  amt 
  FROM dual UNION ALL
  SELECT 'TCTU602388'                         cont_no
       , TO_DATE('26-Dec-2006','dd-Mon-yyyy') cont_date
       , 'SPOTTER'                            description
       , 1250                                 amt 
  FROM dual UNION ALL
  SELECT 'TCTU602388'                         cont_no
       , TO_DATE('26-Dec-2006','dd-Mon-yyyy') cont_date
       , 'OVERTRADE'                          description
       , 3000                                 amt 
  FROM dual UNION ALL
  SELECT 'TCTU602389'                         cont_no
       , TO_DATE('26-Dec-2006','dd-Mon-yyyy') cont_date
       , 'ACTUAL'                             description
       , 500                                  amt 
  FROM dual UNION ALL
  SELECT 'TCTU602389'                         cont_no
       , TO_DATE('26-Dec-2006','dd-Mon-yyyy') cont_date
       , 'OVERTRADE'                          description
       , 3030                                 amt 
  FROM dual UNION ALL
  SELECT 'TCTU602389'                         cont_no
       , TO_DATE('26-Dec-2006','dd-Mon-yyyy') cont_date
       , 'SPOTTER'	                          description
       , 350                                  amt 
  FROM dual 
)
-- the part above can be ignored, it is to simulate your table.
-- the real select begins here:
SELECT cont_no
     , cont_date
     , MAX( DECODE( description
                  , 'ACTUAL'
                  , amt
                  , NULL
                  ) 
          )a_amt
     , MAX( DECODE( description
                  , 'OVERTRADE'
                  , amt
                  , NULL
                  )
          )  o_amt
     , MAX( DECODE( description
                  , 'SPOTTER'
                  , amt
                  , NULL
                  )
           )   s_amt
FROM yourtable
GROUP BY cont_no, cont_date
/


My output:
SQL> @orafaq

CONT_NO    CONT_DATE      A_AMT      O_AMT      S_AMT
---------- --------- ---------- ---------- ----------
TCTU602388 26-DEC-06        500       3000       1250
TCTU602389 26-DEC-06        500       3030        350


MHE
Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227420 is a reply to message #227402] Wed, 28 March 2007 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post create table and insert statements and I show you how to do this.

Regards
Michel
Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227421 is a reply to message #227420] Wed, 28 March 2007 03:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 28 March 2007 10:42

Post create table and insert statements and I show you how to do this.

Regards
Michel


Excellent advice! Read the first post in this forum ("tips and tricks").

MHE
Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227425 is a reply to message #227421] Wed, 28 March 2007 03:54 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
CREATE TABLE CSS_SPL_COMM
(
CONT_NO VARCHAR2(10),
CONT_DATE DATE,
DESCRIPTION VARCHAR2(50) NOT NULL,
"COMM AMT" NUMBER NOT NULL
)
TABLESPACE CSSU
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;

INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU602389', TO_Date( '12/26/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ACTUAL COMM'
, 500);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU602389', TO_Date( '12/26/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'SPOTTER''S COMM'
, 1250);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU602389', TO_Date( '12/26/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'OVERTRADE COMM'
, 3000);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU602388', TO_Date( '12/26/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ACTUAL COMM'
, 500);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU602388', TO_Date( '12/26/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'SPOTTER''S COMM'
, 1250);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU602388', TO_Date( '12/26/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'OVERTRADE COMM'
, 3000);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700102', TO_Date( '01/13/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ACTUAL COMM'
, 750);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700096', TO_Date( '01/12/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ACTUAL COMM'
, 500);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700096', TO_Date( '01/12/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'TAILGATE COMM'
, 738);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700096', TO_Date( '01/12/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'SPOTTER''S COMM'
, 900);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700102', TO_Date( '01/13/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'BOX COMM'
, 500);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700102', TO_Date( '01/13/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'DEALER COMM - EK AUTO (EXPORT) PTE LTD'
, 900);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700153', TO_Date( '01/16/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ACTUAL COMM'
, 750);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700153', TO_Date( '01/16/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'CANOPY COMM'
, 150);
INSERT INTO CSS_SPL_COMM ( CONT_NO, CONT_DATE, DESCRIPTION,
"COMM AMT" ) VALUES (
'TCTU700153', TO_Date( '01/16/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'SPOTTER COMM (KRAFTWAGEN)'
, 1450);
COMMIT;


Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227427 is a reply to message #227425] Wed, 28 March 2007 04:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Unfortunately, I don't see an easy way to make this fully dynamic. You need to know WHAT you select (which columns). Do you know all the possible values of description? If so, you can add a decode for each value.

MHE
Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227430 is a reply to message #227427] Wed, 28 March 2007 04:07 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi MHE,

The problem is I don't know the possible value of the description, users enter there own description.
Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227437 is a reply to message #227430] Wed, 28 March 2007 04:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That's a VERY bad idea. How can you, in advance, know how many columns you're going to get? You have to know what columns you are about to select. There's no way around it, I'm afraid. You don't want to go into dynamic SQL for this one.

Descriptions should be just that: describe something. They shouldn't be used like this. I'd opt for a code table with limited possible values (ACTUAL, OVERTRADE, SPOTTER, ...).

This is a bad design. I hope, for you, that there is still a way to change that.

MHE
Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227448 is a reply to message #227425] Wed, 28 March 2007 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have no time to do it for your exemple but go to one of the following links, I posted exemples on how to do it.
This was times ago and I think these queries can be enhanced.

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=8780#33229
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063#51542758465483

Regards
Michel
Re: Please help me in Dynamic Row to Column in Oracle 8i [message #227568 is a reply to message #227402] Wed, 28 March 2007 12:08 Go to previous message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
You should be able to apply the same method that I provided in my response to your previous post of a similar problem:

http://www.orafaq.com/forum/m/224393/43710/#msg_224393

It should be even simpler, because you only need the dynamic query, not the concatenation.
Previous Topic: Multiple files loading using SQL Loader
Next Topic: pl/sql code
Goto Forum:
  


Current Time: Fri Dec 02 12:30:31 CST 2016

Total time taken to generate the page: 0.09827 seconds