Home » SQL & PL/SQL » SQL & PL/SQL » Row to Column (Oracle, 8.1.7,0.0 / Server Manager 3.1.7.0.0 / Windows Server 2003)
Row to Column [message #419547] Mon, 24 August 2009 03:42 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear Experts,

I have the following query and the result in the attached txt file.

  SELECT   monyy,
           model,
           TYPE,
           lbr,
           mtl,
           parts,
           total
    FROM   mss_v_row_to_col_inv_summ
ORDER BY   1, 3, 2;


if you see the attached file the months will be in rows and the lbr,mtl,parts in column. I want the months in columnwise and the lbr,mtl,parts in rowwise. I have given the create script and sample datas. Please kindly help

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;



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
   ('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
   ('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
   ('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);
COMMIT;


Re: Row to Column [message #419568 is a reply to message #419547] Mon, 24 August 2009 05:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In what the many solutions about this point already posted here and not so far than a couple of hours ago does not work for you?

Regards
Michel
Previous Topic: creatingf function
Next Topic: dblink
Goto Forum:
  


Current Time: Sat Dec 03 16:30:41 CST 2016

Total time taken to generate the page: 0.07634 seconds