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 Go to next message
kumarvk
Messages: 211
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 #326866 is a reply to message #326862] Thu, 12 June 2008 22:13 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
78 post and you still haven't figured out how to format your posts?

You may want to search this site for pivot.
Re: Help in Row to Col [message #326898 is a reply to message #326862] Fri, 13 June 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! This is a new score: this week one question per day on pivot query.

Seems noone is able to search. Sad

Regards
Michel
Re: Help in Row to Col [message #326907 is a reply to message #326898] Fri, 13 June 2008 01:01 Go to previous messageGo to next message
kumarvk
Messages: 211
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 #326909 is a reply to message #326907] Fri, 13 June 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but my column MDL as got more than 150 records so I need to write max(decode)) for 150 records

What is this developer obsession on counting the number of characters they wrote?

By the way how do you display 150 columns?

Regards
Michel
Re: Help in Row to Col [message #326911 is a reply to message #326909] Fri, 13 June 2008 01:14 Go to previous messageGo to next message
kumarvk
Messages: 211
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Help in Row to Col [message #326943 is a reply to message #326911] Fri, 13 June 2008 02:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063

Search for the post dated May 18, 2005 - 10am and the reply from Tom for the question. I think this is what you want.

Regards

Raj
Re: Help in Row to Col [message #327072 is a reply to message #326862] Fri, 13 June 2008 11:25 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you are interested in data pivoting in sql, try these:

Kevin Meade's OraFAQ Blog

Example of Data Pivots in SQL (rows to columns and columns to rows)

Good Luck, Kevin
Previous Topic: how to select 1st and 15th of every month
Next Topic: Crosstab query help needed
Goto Forum:
  


Current Time: Sat Dec 03 03:57:35 CST 2016

Total time taken to generate the page: 0.12948 seconds