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;

Search asktom for "pivot table". You will get tons of examples out there.

Sergey

Hi, This article discusses converting row data to columns
http://www.astral-consultancy.co.uk/...doco.cgi?11310
Hope it helps