Home » SQL & PL/SQL » SQL & PL/SQL » Help in Row to Col (Oracle, 8.1.7, Windows Server 2003)
| Help in Row to Col [message #326862] |
Thu, 12 June 2008 21:38  |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
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;
|
|
|
|
|
|
|
|
| Re: Help in Row to Col [message #326907 is a reply to message #326898] |
Fri, 13 June 2008 01:01  |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
Hi Mr.Michel,
I know about pivot query using max(decode)) ok but my column MDL as got more than 150 records so I need to write max(decode)) for 150 records.
I am looking for any short method to solve this problem. Hope you will understand about it. I tried searching but could not find a suitable answer all explain about max(decode)) only.
|
|
|
|
|
|
| Re: Help in Row to Col [message #326911 is a reply to message #326909] |
Fri, 13 June 2008 01:14 |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
one of my old sql's
MAX(DECODE(B.JOB_CODE,'T0102',A.CUR_MILEAGE)) "SVC-1",
MAX(DECODE(B.JOB_CODE,'T0103',A.CUR_MILEAGE)) "SVC-2",
MAX(DECODE(B.JOB_CODE,'T0104',A.CUR_MILEAGE)) "SVC-3",
MAX(DECODE(B.JOB_CODE,'T0105',A.CUR_MILEAGE)) "SVC-4",
MAX(DECODE(B.JOB_CODE,'T0106',A.CUR_MILEAGE)) "SVC-5",
MAX(DECODE(B.JOB_CODE,'T0107',A.CUR_MILEAGE)) "SVC-6",
MAX(DECODE(B.JOB_CODE,'T0501',A.CUR_MILEAGE)) "OSVC-1",
MAX(DECODE(B.JOB_CODE,'T0502',A.CUR_MILEAGE)) "OSVC-2",
MAX(DECODE(B.JOB_CODE,'T0503',A.CUR_MILEAGE)) "OSVC-3",
MAX(DECODE(B.JOB_CODE,'T0504',A.CUR_MILEAGE)) "OSVC-4",
MAX(DECODE(B.JOB_CODE,'T0505',A.CUR_MILEAGE)) "OSVC-5",
MAX(DECODE(B.JOB_CODE,'T0506',A.CUR_MILEAGE)) "OSVC-6",
MAX(DECODE(B.JOB_CODE,'T0507',A.CUR_MILEAGE)) "OSVC-7",
MAX(DECODE(B.JOB_CODE,'T0508',A.CUR_MILEAGE)) "OSVC-8",
MAX(DECODE(B.JOB_CODE,'T0509',A.CUR_MILEAGE)) "OSVC-9",
MAX(DECODE(B.JOB_CODE,'U0101',A.CUR_MILEAGE)) "UL1_1",
MAX(DECODE(B.JOB_CODE,'U0102',A.CUR_MILEAGE)) "UL1_2",
MAX(DECODE(B.JOB_CODE,'U0103',A.CUR_MILEAGE)) "UL1_3",
MAX(DECODE(B.JOB_CODE,'U0104',A.CUR_MILEAGE)) "UL1_4",
MAX(DECODE(B.JOB_CODE,'U0105',A.CUR_MILEAGE)) "UL1_5",
MAX(DECODE(B.JOB_CODE,'U0106',A.CUR_MILEAGE)) "UL1_6",
MAX(DECODE(B.JOB_CODE,'U0201',A.CUR_MILEAGE)) "UL2_1",
MAX(DECODE(B.JOB_CODE,'U0202',A.CUR_MILEAGE)) "UL2_2",
MAX(DECODE(B.JOB_CODE,'U0203',A.CUR_MILEAGE)) "UL2_3",
MAX(DECODE(B.JOB_CODE,'U0204',A.CUR_MILEAGE)) "UL2_4",
MAX(DECODE(B.JOB_CODE,'U0205',A.CUR_MILEAGE)) "UL2_5",
MAX(DECODE(B.JOB_CODE,'U0206',A.CUR_MILEAGE)) "UL2_6",
MAX(DECODE(B.JOB_CODE,'U0301',A.CUR_MILEAGE)) "UL3_1",
MAX(DECODE(B.JOB_CODE,'U0302',A.CUR_MILEAGE)) "UL3_2",
MAX(DECODE(B.JOB_CODE,'U0303',A.CUR_MILEAGE)) "UL3_3",
MAX(DECODE(B.JOB_CODE,'U0304',A.CUR_MILEAGE)) "UL3_4",
MAX(DECODE(B.JOB_CODE,'U0305',A.CUR_MILEAGE)) "UL3_5",
MAX(DECODE(B.JOB_CODE,'U0306',A.CUR_MILEAGE)) "UL3_6",
MAX(DECODE(B.JOB_CODE,'W0101',A.CUR_MILEAGE)) "WL1_1",
MAX(DECODE(B.JOB_CODE,'W0102',A.CUR_MILEAGE)) "WL1_2",
MAX(DECODE(B.JOB_CODE,'W0103',A.CUR_MILEAGE)) "WL1_3",
MAX(DECODE(B.JOB_CODE,'W0104',A.CUR_MILEAGE)) "WL1_4",
MAX(DECODE(B.JOB_CODE,'W0105',A.CUR_MILEAGE)) "WL1_5",
MAX(DECODE(B.JOB_CODE,'W0106',A.CUR_MILEAGE)) "WL1_6",
MAX(DECODE(B.JOB_CODE,'W0201',A.CUR_MILEAGE)) "WL2_1",
MAX(DECODE(B.JOB_CODE,'W0202',A.CUR_MILEAGE)) "WL2_2",
MAX(DECODE(B.JOB_CODE,'W0203',A.CUR_MILEAGE)) "WL2_3",
MAX(DECODE(B.JOB_CODE,'W0204',A.CUR_MILEAGE)) "WL2_4",
MAX(DECODE(B.JOB_CODE,'W0205',A.CUR_MILEAGE)) "WL2_5",
MAX(DECODE(B.JOB_CODE,'W0206',A.CUR_MILEAGE)) "WL2_6",
MAX(DECODE(B.JOB_CODE,'W0301',A.CUR_MILEAGE)) "WL3_1",
MAX(DECODE(B.JOB_CODE,'W0302',A.CUR_MILEAGE)) "WL3_2",
MAX(DECODE(B.JOB_CODE,'W0303',A.CUR_MILEAGE)) "WL3_3",
MAX(DECODE(B.JOB_CODE,'W0304',A.CUR_MILEAGE)) "WL3_4",
MAX(DECODE(B.JOB_CODE,'W0305',A.CUR_MILEAGE)) "WL3_5",
MAX(DECODE(B.JOB_CODE,'W0306',A.CUR_MILEAGE)) "WL3_6",
So I need to write like this for all 175 MDL's!
|
|
|
|
| Re: Help in Row to Col [message #326918 is a reply to message #326911] |
Fri, 13 June 2008 01:49 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| kumarvk wrote on Fri, 13 June 2008 08:14 |
So I need to write like this for all 175 MDL's!
|
So?
Ever heard of copy-paste? Using macros in editors?
Does your keyboard wear down from typing?
Sometimes you just have to type a lot. Comes with the job.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 07:34:12 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
