Home » SQL & PL/SQL » SQL & PL/SQL » Record to column display ... (Oracle9i, WINXP.)
|
|
Re: Record to column display ... [message #354019 is a reply to message #354015] |
Thu, 16 October 2008 02:14   |
fortunethiyagu
Messages: 94 Registered: December 2006
|
Member |
 
|
|
Iam really sorry yaar for not giving exact points.
Iam having a table that contains 3 coulmns which is given below.
CREATE TABLE temp(tablekey varchar(10),fieldname varchar(15),olddata varchar(15));
Records are :
INSERT INTO temp values('C18CVBNK89','ACTIVE','1');
INSERT INTO temp values('C18CVBNK89','ENDDATE','NULL');
INSERT INTO temp values('C18CVBNK89','ID','01Z041782NH');
INSERT INTO temp values('C18CVBNK89','STARTDATE','01-JAN-00');
INSERT INTO temp values('C18CVBNK89','TYPE_RTK','C1X111AP59');
INSERT INTO temp values('C18CVBNK89','ENDDATE','27-OCT-05');
COMMIT;
selection of records returns:
TABLEKEY FIELDNAME OLDDATA
---------- --------------- ---------------
C18CVBNK89 ACTIVE 1
C18CVBNK89 ENDDATE NULL
C18CVBNK89 ID 01Z041782NH
C18CVBNK89 STARTDATE 01-JAN-00
C18CVBNK89 TYPE_RTK C1X111AP59
C18CVBNK89 ENDDATE 27-OCT-05
Client wants the same to be displayed as each and every record into column for a single tablekey, which is shown belom.
TABLEKEY ACTIVE ENDDATE ID STARTDATE TYPE_RTK ENDDATE_1
---------- ---------- ---------- ----------- ---------- ---------- ----------
C18CVBNK89 1 01Z041782NH 01-JAN-00 C1X111AP59 27-OCT-05
I think now iam clear.
Thanks,
Thiyagu
|
|
|
|
|
|
|
|
|
|
|
|
Re: Record to column display ... [message #354082 is a reply to message #354012] |
Thu, 16 October 2008 06:40   |
spmano1983
Messages: 269 Registered: September 2007
|
Senior Member |
|
|
SELECT tablekey, MAX (DECODE (rn, 1, olddata)) AS "ACTIVE",
MAX (DECODE (rn, 2, olddata)) AS "ENDDATE",
MAX (DECODE (rn, 3, olddata)) AS "ID",
MAX (DECODE (rn, 4, olddata)) AS "STARTDATE",
MAX (DECODE (rn, 5, olddata)) AS "TYPE_RTK",
MAX (DECODE (rn, 6, olddata)) AS "ENDATE_1"
FROM (SELECT tem.*, ROWNUM rn
FROM tem)
GROUP BY tablekey
|
|
|
Re: Record to column display ... [message #354083 is a reply to message #354012] |
Thu, 16 October 2008 06:42   |
spmano1983
Messages: 269 Registered: September 2007
|
Senior Member |
|
|
hope this will help you
SELECT tablekey, MAX (DECODE (rn, 1, olddata)) AS "ACTIVE",
MAX (DECODE (rn, 2, olddata)) AS "ENDDATE",
MAX (DECODE (rn, 3, olddata)) AS "ID",
MAX (DECODE (rn, 4, olddata)) AS "STARTDATE",
MAX (DECODE (rn, 5, olddata)) AS "TYPE_RTK",
MAX (DECODE (rn, 6, olddata)) AS "ENDATE_1"
FROM (SELECT tem.*, ROW_NUMBER() OVER (PARTITION BY TABLEKEY ORDER BY TABLEKEY) RN
FROM tem)
GROUP BY tablekey
|
|
|
Re: Record to column display ... [message #354092 is a reply to message #354012] |
Thu, 16 October 2008 07:09   |
halim
Messages: 100 Registered: September 2008
|
Senior Member |

|
|
previous was fine
another simple is
SELECT tablekey, MAX (DECODE (fieldname, 'ACTIVE', olddata)) active,
MAX (DECODE (fieldname, 'ENDDATE', olddata)) enddate,
MAX (DECODE (fieldname, 'ID', olddata)) ID,
MAX (DECODE (fieldname, 'STARTDATE', olddata)) startdate,
MAX (DECODE (fieldname, 'TYPE_RTK', olddata)) type_rtk
FROM temp
GROUP BY tablekey
ORDER BY tablekey
|
|
|
|
|
|
Re: Record to column display ... [message #354126 is a reply to message #354108] |
Thu, 16 October 2008 08:45  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
OP has to FIRST explain the order he wants BEFORE anyone can post something.
What is it silly? Let's think about it.
I have 20 cars, I put each cars in a room that has the same color of the car, now for each room I number the cars from ...their color. Silly isn't it? Ask you child to do it and you will see.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Mon Feb 17 20:32:37 CST 2025
|