Home » SQL & PL/SQL » SQL & PL/SQL » Please Help in Pivot Query (Oracle, 8.1.7,0.0 / Server Manager 3.1.7.0.0 / Windows Server 2003)
Please Help in Pivot Query [message #419663] Mon, 24 August 2009 23:01 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

I did the following pivot query but getting error message.

  SELECT   DISTINCT MODEL,
                    TYPE,
                    MAX (DECODE (MONYY, 'JAN 08', SUM (lbr), 0)) jan08,
                    MAX (DECODE (MONYY, 'FEB 08', SUM (lbr), 0)) feb08,
                    MAX (DECODE (MONYY, 'MAR 08', SUM (lbr), 0)) mar08
    FROM   mss_v_row_to_col_inv_summ
GROUP BY   MODEL, TYPE;


I am getting error message ORA-00937: not a single-group group function

I want the output in the following format

MODEL TYPE JAN08 FEB08 MAR08
BUS CASH
BUS CREDIT
BUS INTERNAL
BUS WARRANTY
F MEDIUM DUTY TRUCK CASH
F MEDIUM DUTY TRUCK CREDIT
F MEDIUM DUTY TRUCK INTERNAL
F MEDIUM DUTY TRUCK WARRANTY

Table script

CREATE TABLE test
(
  MONYY  VARCHAR2(6),
  MODEL  VARCHAR2(20),
  TYPE   VARCHAR2(8),
  LBR    NUMBER,
  MTL    NUMBER,
  PARTS  NUMBER,
  TOTAL  NUMBER
)
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:

SET DEFINE OFF;
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'BUS', 'CASH', 0, 45, 
    0, 45);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'C&E HEAVY DUTY TRUCK', 'CASH', 913.39, 307, 
    2036.8, 3257.19);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'F MEDIUM DUTY TRUCK', 'CASH', 491.8, 2435.5, 
    496.8, 3424.1);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'N LIGHT DUTY TRUCK', 'CASH', 10244.8, 6229.3, 
    15025.9, 31500);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'TF PICK-UP', 'CASH', 879.5, 429.7, 
    1255.6, 2564.8);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'C&E HEAVY DUTY TRUCK', 'CASH', 9210.6, 535.7, 
    3285.6, 13031.9);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'F MEDIUM DUTY TRUCK', 'CASH', 4596.3, 2411.2, 
    8138, 15145.5);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'N LIGHT DUTY TRUCK', 'CASH', 12360.1, 7608.1, 
    8823.3, 28791.5);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'TF PICK-UP', 'CASH', 3403.2, 665.2, 
    1993.6, 6062);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'BUS', 'CASH', 188, 159.7, 
    201.2, 548.9);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'C&E HEAVY DUTY TRUCK', 'CASH', 356, 389.4, 
    428, 1173.4);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'F MEDIUM DUTY TRUCK', 'CASH', 446.8, 497.9, 
    572.95, 1517.65);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'N LIGHT DUTY TRUCK', 'CASH', 10008.5, 9563.4, 
    12884.8, 32456.7);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'TF PICK-UP', 'CASH', 1418.65, 1166, 
    1615.5, 4200.15);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'C&E HEAVY DUTY TRUCK', 'CREDIT', 1116, 4699.4, 
    256, 6071.4);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'F MEDIUM DUTY TRUCK', 'CREDIT', 405.6, 348.7, 
    152, 906.3);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'N LIGHT DUTY TRUCK', 'CREDIT', 13425.2, 8167.5, 
    4180.5, 25773.2);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'TF PICK-UP', 'CREDIT', 3725.4, 5658.1, 
    1027.2, 10410.7);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'BUS', 'CREDIT', 152.1, 13, 
    566.15, 731.25);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'C&E HEAVY DUTY TRUCK', 'CREDIT', 1372.5, 8626.4, 
    139.2, 10138.1);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'F MEDIUM DUTY TRUCK', 'CREDIT', 3038.1, 1005.2, 
    1618.4, 5661.7);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'N LIGHT DUTY TRUCK', 'CREDIT', 16573, 22733.3, 
    1883.2, 41189.5);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'TF PICK-UP', 'CREDIT', 4201.7, 5785.5, 
    5851.2, 15838.4);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'BUS', 'CREDIT', 0, 900, 
    0, 900);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'C&E HEAVY DUTY TRUCK', 'CREDIT', 17263, 2015.4, 
    2580, 21858.4);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'F MEDIUM DUTY TRUCK', 'CREDIT', 469.8, 16.4, 
    866.4, 1352.6);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'N LIGHT DUTY TRUCK', 'CREDIT', 18240.9, 26389.3, 
    2735.8, 47366);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'TF PICK-UP', 'CREDIT', 1546.9, 2419.6, 
    3017.6, 6984.1);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'BUS', 'INTERNAL', 3652.2, 4636.6, 
    1349.63, 9638.43);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'C&E HEAVY DUTY TRUCK', 'INTERNAL', 26173.4, 402586.6, 
    12987.69, 441747.69);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'F MEDIUM DUTY TRUCK', 'INTERNAL', 5405.4, 117655.1, 
    2461.5, 125522);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'N LIGHT DUTY TRUCK', 'INTERNAL', 51253.93, 227874, 
    17642.15, 296770.08);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'TF PICK-UP', 'INTERNAL', 986.2, 3039.5, 
    879.75, 4905.45);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'TROOPER', 'INTERNAL', 0, 300, 
    0, 300);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'BUS', 'INTERNAL', 5863.9, 6382.4, 
    1055.25, 13301.55);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'C&E HEAVY DUTY TRUCK', 'INTERNAL', 58932.8, 545721.6, 
    30424.85, 635079.25);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'F MEDIUM DUTY TRUCK', 'INTERNAL', 6053.6, 57812.7, 
    2436.37, 66302.67);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'N LIGHT DUTY TRUCK', 'INTERNAL', 48049.64, 210568.8, 
    15359.39, 273977.83);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'TF PICK-UP', 'INTERNAL', 2376.6, 4885.8, 
    1638.9, 8901.3);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'BUS', 'INTERNAL', 8179.75, 9399.4, 
    1384.03, 18963.18);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'C&E HEAVY DUTY TRUCK', 'INTERNAL', 57220, 452687.3, 
    44511.16, 554418.46);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'F MEDIUM DUTY TRUCK', 'INTERNAL', 11793.6, 54453.9, 
    3116.65, 69364.15);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'N LIGHT DUTY TRUCK', 'INTERNAL', 121867.17, 419661.1, 
    21237.1, 562765.37);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'TF PICK-UP', 'INTERNAL', 5979.3, 16857.3, 
    4150.87, 26987.47);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'BUS', 'WARRANTY', 668, 0, 
    577.72, 1245.72);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'C&E HEAVY DUTY TRUCK', 'WARRANTY', 1484, 440.8, 
    8182.97, 10107.77);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'F MEDIUM DUTY TRUCK', 'WARRANTY', 6013, 30, 
    2933.52, 8976.52);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'N LIGHT DUTY TRUCK', 'WARRANTY', 14693, 8155.4, 
    28195.42, 51043.82);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('FEB 08', 'TF PICK-UP', 'WARRANTY', 389, 168.2, 
    419.43, 976.63);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'BUS', 'WARRANTY', 1298, 1254, 
    283.91, 2835.91);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'C&E HEAVY DUTY TRUCK', 'WARRANTY', 3106, 2605, 
    23264.29, 28975.29);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'F MEDIUM DUTY TRUCK', 'WARRANTY', 11752.8, 6887.4, 
    28490.48, 47130.68);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'N LIGHT DUTY TRUCK', 'WARRANTY', 21811, 8421.1, 
    35424.1, 65656.2);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('JAN 08', 'TF PICK-UP', 'WARRANTY', 420, 186.4, 
    1748.65, 2355.05);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'BUS', 'WARRANTY', 971, 8.2, 
    1032.29, 2011.49);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'C&E HEAVY DUTY TRUCK', 'WARRANTY', 1308, 1710, 
    37173.57, 40191.57);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'F MEDIUM DUTY TRUCK', 'WARRANTY', 4561, 80, 
    1108.93, 5749.93);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'N LIGHT DUTY TRUCK', 'WARRANTY', 8670, 2417.7, 
    21923.68, 33011.38);
Insert into TEST
   (MONYY, MODEL, TYPE, LBR, MTL, 
    PARTS, TOTAL)
 Values
   ('MAR 08', 'TF PICK-UP', 'WARRANTY', 136, 0, 
    279.62, 415.62);
COMMIT;

Re: Please Help in Pivot Query [message #419664 is a reply to message #419663] Mon, 24 August 2009 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>(Oracle, 8.1.7,0.0 / Server Manager 3.1.7.0.0 / Windows Server 2003)
Oracle V8 obsoleted last century & 100% unsupported on Windoze 2003.

>MONYY VARCHAR2(6),
Fatally flawed "design"

INSERT INTO TEST (MONYY) VALUES (IDIOT!); -- as posted 100% valid

DDML & DML for table TEST while SELECT FROM mss_v_row_to_col_inv_summ

Re: Please Help in Pivot Query [message #419667 is a reply to message #419663] Mon, 24 August 2009 23:24 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
  SELECT   DISTINCT MODEL,
                    TYPE,
                    MAX (DECODE (MONYY, 'JAN 08', SUM (lbr), 0)) jan08,
                    MAX (DECODE (MONYY, 'FEB 08', SUM (lbr), 0)) feb08,
                    MAX (DECODE (MONYY, 'MAR 08', SUM (lbr), 0)) mar08
    FROM   mss_v_row_to_col_inv_summ
GROUP BY   MODEL, TYPE;

1/ You don't need DISTINCT if you group by the same columns.
2/ Not "max(decode(sum" but "sum(decode". Try to understand what you write.

Regards
Michel

[Updated on: Mon, 24 August 2009 23:24]

Report message to a moderator

Previous Topic: With RELY DISABLE option not able use the FK constraints
Next Topic: Query for RLS is not activated:
Goto Forum:
  


Current Time: Sun Dec 11 08:29:56 CST 2016

Total time taken to generate the page: 0.04323 seconds