-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|