Home » SQL & PL/SQL » SQL & PL/SQL » Storing table coumn name from source table into column value in target table (Oracle 9.5)
Storing table coumn name from source table into column value in target table [message #392656] Wed, 18 March 2009 15:34 Go to next message
piyali.sriza@gmail.com
Messages: 6
Registered: March 2009
Junior Member
I need to write a procedure in Oracle 9 for data conversion..but not able to proceed...

The source table structure is as following..

EMPLID| MATH | PHYSICS| CHEMISTRY| BENGALI| DATE
---------- --------- --------------- ------------------ --------------- ---------

100 1 0 0 0 01/01/2009

100 0 1 0 1 02/02/2009

100 0 0 1 0 03/03/2009

-------------------------------------------------------------------------------------

Here 1 means course attended..first row signifies that on 01/01/2009 emplid 100 attended course MATH.

Now i need to store the above data in follwing format in another taget table which is PS table..

EMPLID SUBJECT DATE
----------- ----------------- -----------

100 MATH 01/01/2009

100 BENGALI 02/02/2009

100 PHYSICS 02/02/2009

100 CHEMISTRY 03/03/2009


It should take the data for which the value is 1 and store the correspondingcolumn name like MATH,PHYSICS..

column values can be taken..but how to take column names and store it..i cant hardcode also..b coz the source table contains 280 column like MATH.PHYSICS ETC..

Pls suggest...waiting for reply..
Re: Storing table coumn name from source table into column value in target table [message #392657 is a reply to message #392656] Wed, 18 March 2009 15:41 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Read URL above & use <code tags> for all new postings!
Re: Storing table coumn name from source table into column value in target table [message #392707 is a reply to message #392656] Thu, 19 March 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and:
1/ format your post
2/ post a test case
3/ post the actual oracle version (9.5 never existed)
4/ don't use IM speak
...

Quote:
i cant hardcode also..b coz the source table contains 280 column like MATH.PHYSICS ETC..

You wrote them to create the table why won't write them to query the table?

Regards
Michel
Re: Storing table coumn name from source table into column value in target table [message #392729 is a reply to message #392707] Thu, 19 March 2009 01:53 Go to previous messageGo to next message
piyali.sriza@gmail.com
Messages: 6
Registered: March 2009
Junior Member
I didnt understand.

The source table is already there...i need to get the column names (MATH,PHYSICS)of the source table and store it in SUBJECT column of the target table.

How to fetch the column names dynamically and store it another table.

Please suggest.
Re: Storing table coumn name from source table into column value in target table [message #392731 is a reply to message #392729] Thu, 19 March 2009 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2/ post a test case

Regards
Michel
Re: Storing table coumn name from source table into column value in target table [message #392964 is a reply to message #392656] Thu, 19 March 2009 17:38 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>i cant hardcode also..b coz the source table contains 280 column like MATH.PHYSICS ETC..
See how/why this "design" does not scale very well?

Please realize this is a slight challenge for you because the table "design" is very, very sub-optimal.

What prevent having having more than a single 1 in the 200+ columns?

A solution could include use of DECODE.

[Updated on: Thu, 19 March 2009 20:11]

Report message to a moderator

Previous Topic: Help on Query - analytic?
Next Topic: Tranposed output
Goto Forum:
  


Current Time: Sat Dec 03 03:55:37 CST 2016

Total time taken to generate the page: 0.09218 seconds