Help in Row To Col
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help in Row To Col

Hybrid View

  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Help in Row To Col

    Dear All,

    I have a table need to convert the row to col. I already did a sql using concatenate function

    SELECT pno, pname,
    concatenate ('PNO || PNAME',
    pno || pname,
    'MDL',
    'MPS_V_row_to_col'
    ) AS mdl
    FROM mps_v_row_to_col
    GROUP BY pno,PNAME;

    My result comes like this
    PNO MDL PNAME
    0-01558300-0 NPR85,NPR75 BOLT
    0-01804130-0 NPR85,TFR86 NUT
    0-03559300-0 TFR86,CXH51 NAIL
    0-03566300-0 CXH51,NPR85 SCREW
    0-28050816-0 TFR86,DMAX,TFR54,NPR71 KNIFE

    But I want the result of MDL in individual column.

    like

    PNO MDL1 MDL2 MDL3 MDL4 PNAME
    0-01558300-0 NPR85 NPR75 BOLT
    0-01804130-0 NPR85 TFR86 NUT
    0-03559300-0 TFR86 CXH51 NAIL
    0-03566300-0 CXH51 NPR85 SCREW
    0-28050816-0 TFR86 DMAX TFR54 NPR71 KNIFE

    Can anyone help me in this regard.

    My table and sample data with insert is as follows;

    CREATE TABLE MPS.MPS_ROW_TO_COL
    (
    PNO VARCHAR2(22),
    MDL VARCHAR2(10),
    PNAME VARCHAR2(22)
    );

    SET DEFINE OFF;
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-01558300-0', 'NPR85', 'BOLT');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-01558300-0', 'NPR75', 'BOLT');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-01804130-0', 'NPR85', 'NUT');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-01804130-0', 'TFR86', 'NUT');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-03559300-0', 'TFR86', 'NAIL');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-03559300-0', 'CXH51', 'NAIL');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-03566300-0', 'CXH51', 'SCREW');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-03566300-0', 'NPR85', 'SCREW');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-28050816-0', 'TFR86', 'KNIFE');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-28050816-0', 'DMAX', 'KNIFE');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-28050816-0', 'TFR54', 'KNIFE');
    Insert into MPS_ROW_TO_COL
    (PNO, MDL, PNAME)
    Values
    ('0-28050816-0', 'NPR71', 'KNIFE');
    COMMIT;

  2. #2
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Search asktom for "pivot table". You will get tons of examples out there.

    Sergey

  3. #3
    Join Date
    Jan 2007
    Posts
    5
    Hi, This article discusses converting row data to columns
    http://www.astral-consultancy.co.uk/...doco.cgi?11310
    Hope it helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width