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  |
kumarvk
Messages: 184
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  |
 |
Michel Cadot
Messages: 54125
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 #570662 is a reply to message #570559] |
Tue, 13 November 2012 20:38 |
kumarvk
Messages: 184
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 #570677 is a reply to message #570673] |
Wed, 14 November 2012 02:33 |
kumarvk
Messages: 184
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 #570686 is a reply to message #570679] |
Wed, 14 November 2012 04:13 |
kumarvk
Messages: 184
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 |
|
Michel Cadot
Messages: 54125
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 |
Solomon Yakobson
Messages: 1397
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 |
|
Michel Cadot
Messages: 54125
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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 18 11:39:04 CDT 2013
Total time taken to generate the page: 0.11402 seconds
|
] 
Members
Search
Help
Register
Login
Home
