Home » SQL & PL/SQL » SQL & PL/SQL » Record to column display ... (Oracle9i, WINXP.)
Record to column display ... [message #354012] Thu, 16 October 2008 01:31 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

please help me write the query for following condition.

This is the input table,

Tablekey Fieldname value
w1 name thiyagu
w1 city cbe
w1 state tn
w2 name senthur
w2 city chennai
w2 state tn

the output should be,

Tablekey Name City state
w1 thiyagu cbe tn
w2 senthur chennai tn

I tried with lot of queries, but still im struggling ...

please help me out ...

thanks,
thiyagu
Re: Record to column display ... [message #354015 is a reply to message #354012] Thu, 16 October 2008 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

2/
Also always post your Oracle version (4 decimals).

3/
Post a test case: create table and insert statements along with the result you want with these data.

4/
Post what you already tried

5/
This is a VERY VERY BAD design, the badest you can imagine.

Regards
Michel
Re: Record to column display ... [message #354019 is a reply to message #354015] Thu, 16 October 2008 02:14 Go to previous messageGo to next message
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 #354021 is a reply to message #354019] Thu, 16 October 2008 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You were clear, you just didn't follow the guidelines.
You have to self-join the table for each attribute.

Regards
Michel
Re: Record to column display ... [message #354023 is a reply to message #354012] Thu, 16 October 2008 02:31 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
You may be interested in this thread on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056.
Be prepared for very low performance when having thousands rows in TEMP table.
Re: Record to column display ... [message #354024 is a reply to message #354012] Thu, 16 October 2008 02:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or use

Correlated Subquery

Thumbs Up
Rajuvan
Re: Record to column display ... [message #354033 is a reply to message #354024] Thu, 16 October 2008 03:10 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

I did self join, but i couldnt able to split it into multiple columns .. how to split the columns group by tablekey ... can anyone help me?
Re: Record to column display ... [message #354036 is a reply to message #354033] Thu, 16 October 2008 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 16 October 2008 08:36

4/
Post what you already tried

Regards
Michel

Re: Record to column display ... [message #354051 is a reply to message #354036] Thu, 16 October 2008 04:17 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

I tried with this query since lot of tablekeys are there and 12 fields are there in extra.

SELECT a.tablekey,a.fieldname,a.olddata FROM temp a
WHERE  EXISTS 
                          (SELECT b.fieldname FROM temp b
                           WHERE  b.tablekey = a.tablekey)
GROUP BY a.tablekey,a.fieldname,a.olddata
ORDER BY a.tablekey,a.fieldname;


Is there anyway to comeup with column level split?

Thanks,
Thiyagu
Re: Record to column display ... [message #354052 is a reply to message #354036] Thu, 16 October 2008 04:25 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

I tried with this query since lot of tablekeys are there and 12 fields are there in extra. Evn i tried with many queries, which i know that is useless.

SELECT a.tablekey,a.fieldname,a.olddata FROM temp a
WHERE  EXISTS 
                          (SELECT b.fieldname FROM temp b
                           WHERE  b.tablekey = a.tablekey)
GROUP BY a.tablekey,a.fieldname,a.olddata
ORDER BY a.tablekey,a.fieldname;


Is there anyway to comeup with column level split?

Thanks,
Thiyagu
Re: Record to column display ... [message #354054 is a reply to message #354012] Thu, 16 October 2008 04:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Then what is the difference between Normal group by

SELECT a.tablekey,a.fieldname,a.olddata FROM temp a
ORDER BY a.tablekey,a.fieldname


and your query ?

Anyway it is not turning into your solution , as you want output in Column level output.

So you have to have 11 subqueries ( each for each fields ) -- If you go for correlated subquery and
11 joins -- If you go for self joins

Thumbs Up
Rajuvan.
Re: Record to column display ... [message #354067 is a reply to message #354054] Thu, 16 October 2008 06:00 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

I dont think subquery could result, what i am expecting!!! Sad

Anyway thanks all friends ..
icon14.gif  Re: Record to column display ... [message #354082 is a reply to message #354012] Thu, 16 October 2008 06:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
halim
Messages: 99
Registered: September 2008
Location: Bangladesh
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 #354096 is a reply to message #354012] Thu, 16 October 2008 07:16 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

See here two ENDDATE columns are there. that's why i have used row_number()
Re: Record to column display ... [message #354106 is a reply to message #354096] Thu, 16 October 2008 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This clause "PARTITION BY TABLEKEY ORDER BY TABLEKEY" that you love is silly and the 2 enddate columns in result also (as the requirements have been expressed) which one you put where?

Regards
Michel
Re: Record to column display ... [message #354108 is a reply to message #354012] Thu, 16 October 2008 08:00 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi Michel,

Could you please explain, instead of using partition by tablekey order by tablekey which method we can use? If you have any idea better than me please share it.

Re: Record to column display ... [message #354126 is a reply to message #354108] Thu, 16 October 2008 08:45 Go to previous message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: sending email automatically from oracle
Next Topic: How to Split a date (Merged)
Goto Forum:
  


Current Time: Tue Sep 16 11:24:50 CDT 2014

Total time taken to generate the page: 0.06212 seconds