Home » SQL & PL/SQL » SQL & PL/SQL » Help in this pivot query (Oracle 8.0.4.1,Windows 2003 server)
Help in this pivot query [message #570555] Mon, 12 November 2012 04:30 Go to next message
kumarvk
Messages: 210
Registered: July 2004
Senior Member
Hi Experts,

I know that I am using outdated oracle version what to do my company planning to upgrade next year only. I am seeking your expertise help in the following sql. I want the rows to become like column and the column to become like rows.

Current output:
YEAR C_SERIES N_SERIES F_SERIES
2010 128 323 46
2011 267 340 44
2012 301 204 36

Requested output:
MDL/YR 2010 2011 2012
C_SERIES 128 323 46
N_SERIES 267 340 44
F_SERIES 301 204 36

SELECT 
to_char(G.BIHD_DOC_DATE,'YYYY') YEAR,
COUNT(DECODE(SUBSTR(E.CODE,1,2),'CY',G.NO,'')) C_SERIES,
COUNT(DECODE(SUBSTR(E.CODE,1,1),'N',G.NO,'')) N_SERIES,
COUNT(DECODE(SUBSTR(E.CODE,1,1),'F',G.NO,'')) F_SERIES
FROM MVSM D,MVDM E,MVCR G
WHERE BIHD_DOC_DATE BETWEEN TO_DATE('01-JAN-10 00:00:00', 'dd-mon-yy hh24:mi:ss') 
AND TO_DATE('31-OCT-12 23:59:59', 'dd-mon-yy hh24:mi:ss')
AND G.SUBM_ID=D.ID AND D.DCHM_ID=E.ID 
AND substr(E.CODE,1,5) in (
             'CYH52', 'CYZ52',
             'FRR34', 'FSR34', 'FTR34', 'FVR34', 'FRR90', 'FVZ34', 
             'NHR85', 'NNR85', 'NPR75', 'NPR85', 'NQR75', 'NJR85', 
             'NPS75', 'NMR85'
             )
GROUP BY TO_CHAR(BIHD_DOC_DATE,'YYYY');


Can anyone help me?
Re: Help in this pivot query [message #570557 is a reply to message #570555] Mon, 12 November 2012 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT 
  2  to_char(G.BIHD_DOC_DATE,'YYYY') YEAR,
  3  COUNT(DECODE(SUBSTR(E.CODE,1,2),'CY',G.NO,'')) C_SERIES,
  4  COUNT(DECODE(SUBSTR(E.CODE,1,1),'N',G.NO,'')) N_SERIES,
  5  COUNT(DECODE(SUBSTR(E.CODE,1,1),'F',G.NO,'')) F_SERIES
  6  FROM MVSM D,MVDM E,MVCR G
  7  WHERE BIHD_DOC_DATE BETWEEN TO_DATE('01-JAN-10 00:00:00', 'dd-mon-yy hh24:mi:ss') 
  8  AND TO_DATE('31-OCT-12 23:59:59', 'dd-mon-yy hh24:mi:ss')
  9  AND G.SUBM_ID=D.ID AND D.DCHM_ID=E.ID 
 10  AND substr(E.CODE,1,5) in (
 11               'CYH52', 'CYZ52',
 12               'FRR34', 'FSR34', 'FTR34', 'FVR34', 'FRR90', 'FVZ34', 
 13               'NHR85', 'NNR85', 'NPR75', 'NPR85', 'NQR75', 'NJR85', 
 14               'NPS75', 'NMR85'
 15               )
 16  GROUP BY TO_CHAR(BIHD_DOC_DATE,'YYYY');
FROM MVSM D,MVDM E,MVCR G
                   *
ERROR at line 6:
ORA-00942: table or view does not exist

Also, are you sure your Oracle RDBMS version is 8.0.4.1?

Regards
Michel
Re: Help in this pivot query [message #570558 is a reply to message #570557] Mon, 12 November 2012 04:51 Go to previous messageGo to next message
kumarvk
Messages: 210
Registered: July 2004
Senior Member
Sorry Mr.Michel the version 8.1.7.0.0
Re: Help in this pivot query [message #570559 is a reply to message #570558] Mon, 12 November 2012 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, now the other usual points to provide (hint: see the error I got).

Regards
Michel
Re: Help in this pivot query [message #570662 is a reply to message #570559] Tue, 13 November 2012 20:38 Go to previous messageGo to next message
kumarvk
Messages: 210
Registered: July 2004
Senior Member
Hi Michel,

Sorry for the delay in providing the details of the table and insert statements.

create table script
CREATE TABLE MVS
(
  CAR_NO    VARCHAR2(9),
  DOC_DATE  DATE,
  CODE      VARCHAR2(2)
)
TABLESPACE MSSU
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;


INSERT STATEMENTS
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00893', TO_DATE('03/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00827', TO_DATE('12/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00894', TO_DATE('03/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00791', TO_DATE('03/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00771', TO_DATE('02/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00907', TO_DATE('04/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00925', TO_DATE('07/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01282', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01401', TO_DATE('10/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01403', TO_DATE('10/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00725', TO_DATE('07/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00749', TO_DATE('07/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00726', TO_DATE('07/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00712', TO_DATE('07/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00709', TO_DATE('07/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00744', TO_DATE('07/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01035', TO_DATE('12/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01370', TO_DATE('09/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01166', TO_DATE('02/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01348', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01175', TO_DATE('02/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01176', TO_DATE('02/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01335', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01336', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01337', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01164', TO_DATE('02/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00764', TO_DATE('04/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00765', TO_DATE('04/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00766', TO_DATE('04/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00767', TO_DATE('04/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY00768', TO_DATE('04/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01226', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01227', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01228', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01173', TO_DATE('02/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01120', TO_DATE('12/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01174', TO_DATE('02/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01177', TO_DATE('02/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01229', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01230', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01346', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01347', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01353', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01351', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01352', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TFR00170', TO_DATE('09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'FR');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TFS00249', TO_DATE('03/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'FS');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TFV00071', TO_DATE('04/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'FV');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TFV00087', TO_DATE('09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'FV');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03210', TO_DATE('02/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03205', TO_DATE('11/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03195', TO_DATE('11/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03341', TO_DATE('10/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03004', TO_DATE('03/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03067', TO_DATE('07/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03128', TO_DATE('04/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03196', TO_DATE('12/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03124', TO_DATE('04/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03102', TO_DATE('04/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03139', TO_DATE('07/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03204', TO_DATE('11/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03472', TO_DATE('03/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03579', TO_DATE('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03539', TO_DATE('07/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03739', TO_DATE('06/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03751', TO_DATE('03/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03710', TO_DATE('11/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03569', TO_DATE('02/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03570', TO_DATE('02/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03702', TO_DATE('09/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03572', TO_DATE('02/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03606', TO_DATE('09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00206', TO_DATE('12/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00178', TO_DATE('09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00253', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00184', TO_DATE('02/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00183', TO_DATE('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00179', TO_DATE('02/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00181', TO_DATE('02/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00182', TO_DATE('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00227', TO_DATE('06/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00203', TO_DATE('12/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00215', TO_DATE('04/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00233', TO_DATE('06/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNN00280', TO_DATE('06/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NN');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNQ00198', TO_DATE('10/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NQ');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNQ00246', TO_DATE('12/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NQ');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03568', TO_DATE('09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03600', TO_DATE('06/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03596', TO_DATE('06/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03597', TO_DATE('06/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03598', TO_DATE('06/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03599', TO_DATE('06/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNJ00052', TO_DATE('04/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NJ');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNM00034', TO_DATE('03/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NM');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNM00002', TO_DATE('06/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NM');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNM00055', TO_DATE('06/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NM');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNM00063', TO_DATE('07/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NM');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNM00058', TO_DATE('06/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NM');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03609', TO_DATE('09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03620', TO_DATE('04/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03619', TO_DATE('12/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03641', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03640', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03639', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNP03625', TO_DATE('08/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NP');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TCY01058', TO_DATE('09/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CY');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TFV00124', TO_DATE('12/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'FV');
Insert into MSS_V_VEH_SALES
   (CAR_NO, DOC_DATE, CODE)
 Values
   ('TNH03294', TO_DATE('10/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NH');
COMMIT;
Re: Help in this pivot query [message #570673 is a reply to message #570662] Wed, 14 November 2012 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What should be the result from the test case you gave?

Regards
Michel
Re: Help in this pivot query [message #570677 is a reply to message #570673] Wed, 14 November 2012 02:33 Go to previous messageGo to next message
kumarvk
Messages: 210
Registered: July 2004
Senior Member
Hi,

My current result like this

YEAR CY N F
2010 128 323 46
2011 267 340 44
2012 301 204 36

I want the current row to become like column and the column to be become like row. Like the following

MODEL 2010 2011 2012
C 128 267 301
F 46 44 36
N 323 340 204

Thanks

Re: Help in this pivot query [message #570679 is a reply to message #570677] Wed, 14 November 2012 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is 128 for instance?

Regards
Michel
Re: Help in this pivot query [message #570684 is a reply to message #570677] Wed, 14 November 2012 04:08 Go to previous messageGo to next message
kumarvk
Messages: 210
Registered: July 2004
Senior Member
Hi,

It is the count of car_no.
Re: Help in this pivot query [message #570686 is a reply to message #570679] Wed, 14 November 2012 04:13 Go to previous messageGo to next message
kumarvk
Messages: 210
Registered: July 2004
Senior Member
Hi,

my code
  SELECT   TO_CHAR (MVS.DOC_DATE, 'YYYY') YEAR,
           COUNT (DECODE (SUBSTR (MVS.CODE, 1, 2), 'CY', MVS.CAR_NO, ''))
              C_SERIES,
           COUNT (DECODE (SUBSTR (MVS.CODE, 1, 1), 'N', MVS.CAR_NO, ''))
              N_SERIES,
           COUNT (DECODE (SUBSTR (MVS.CODE, 1, 1), 'F', MVS.CAR_NO, ''))
              F_SERIES
    FROM   MVS
   WHERE   MVS.DOC_DATE BETWEEN TO_DATE ('01-JAN-10 00:00:00',
                                         'dd-mon-yy hh24:mi:ss')
                            AND  TO_DATE ('31-OCT-12 23:59:59',
                                          'dd-mon-yy hh24:mi:ss')
GROUP BY   TO_CHAR (MVS.DOC_DATE, 'YYYY');
Re: Help in this pivot query [message #570687 is a reply to message #570686] Wed, 14 November 2012 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I ran your code with your test case and does not get what you say:
SQL> SELECT   TO_CHAR (MVS.DOC_DATE, 'YYYY') YEAR,
  2             COUNT (DECODE (SUBSTR (MVS.CODE, 1, 2), 'CY', MVS.CAR_NO, ''))
  3                C_SERIES,
  4             COUNT (DECODE (SUBSTR (MVS.CODE, 1, 1), 'N', MVS.CAR_NO, ''))
  5                N_SERIES,
  6             COUNT (DECODE (SUBSTR (MVS.CODE, 1, 1), 'F', MVS.CAR_NO, ''))
  7                F_SERIES
  8      FROM   MVS
  9     WHERE   MVS.DOC_DATE BETWEEN TO_DATE ('01-JAN-10 00:00:00',
 10                                           'dd-mon-yy hh24:mi:ss')
 11                              AND  TO_DATE ('31-OCT-12 23:59:59',
 12                                            'dd-mon-yy hh24:mi:ss')
 13  GROUP BY   TO_CHAR (MVS.DOC_DATE, 'YYYY');
YEAR   C_SERIES   N_SERIES   F_SERIES
---- ---------- ---------- ----------
2010         14         28          3
2011          7         19          2
2012         25         11          0

Regards
Michel
Re: Help in this pivot query [message #570691 is a reply to message #570686] Wed, 14 November 2012 04:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1788
Registered: January 2010
Senior Member
Using PIVOT:

select  *
  from  (
         select  trunc(doc_date,'yyyy') doc_date,
                 case
                   when code like 'CY%' then 'CY'
                   else substr(code,1,1)
                 end code
           from  mss_v_veh_sales
        )
  pivot (count(*) for code in ('CY' cy,'N' n,'F' f))
/

DOC_DATE   CY   N   F
--------- --- --- ---
01-JAN-10  14  28   3
01-JAN-12  25  11   0
01-JAN-11   7  19   2

SQL> 


SY.
Re: Help in this pivot query [message #570692 is a reply to message #570687] Wed, 14 November 2012 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe this is what you want:
SQL> select substr(code,1,1) model,
  2         count(decode(extract(year from doc_date), 2010, 1)) "2010",
  3         count(decode(extract(year from doc_date), 2011, 1)) "2011",
  4         count(decode(extract(year from doc_date), 2012, 1)) "2012"
  5  from mvs
  6  group by substr(code,1,1)
  7  order by substr(code,1,1)
  8  /
M       2010       2011       2012
- ---------- ---------- ----------
C         14          7         25
F          3          2          0
N         28         19         11

But who knows you did not specify what you want?

Regards
Michel
Re: Help in this pivot query [message #570694 is a reply to message #570691] Wed, 14 November 2012 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Using PIVOT:


kumarvk wrote on Mon, 12 November 2012 11:51
Sorry Mr.Michel the version 8.1.7.0.0


Regards
Michel

[Updated on: Wed, 14 November 2012 05:02]

Report message to a moderator

Re: Help in this pivot query [message #570789 is a reply to message #570692] Thu, 15 November 2012 18:55 Go to previous message
kumarvk
Messages: 210
Registered: July 2004
Senior Member
Thanks Michel
Previous Topic: Bitmap index rebuild - getting ORA-00054
Next Topic: traverse a table
Goto Forum:
  


Current Time: Thu Apr 17 00:29:43 CDT 2014

Total time taken to generate the page: 0.09986 seconds