Home » SQL & PL/SQL » SQL & PL/SQL » select query to take row value as column name
select query to take row value as column name [message #423727] Sun, 27 September 2009 08:42 Go to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
Hi All,

My issue is that i have to take row values as column name. Here i am taking HRMS tables like per_elements_type_f table i have to select elemnt name (eg Cost To Company,HRA,LTA
etc) and these for each employee i hve to find out the values for these so my table struecture has
be like as shown below:

Employee Number Emp Name CTC HRA
1 ABC 1000000 5000
2 XYZ 2000000 8000

soo this CTC,HRA etc are column value from one table and these values are also column value from
othere table so can anybody suggest me that how to write a select query for this case?

Best Regards,
Monika

Re: select query to take row value as column name [message #423729 is a reply to message #423727] Sun, 27 September 2009 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your client program? You cannot do it in a single SQL statement. You have to precise your environment.
Post a working Test case: create table and insert statements along with the result you want with these data.

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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Sun, 27 September 2009 09:04]

Report message to a moderator

Re: select query to take row value as column name [message #423731 is a reply to message #423729] Sun, 27 September 2009 09:20 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
Hi Michel,
my table structure are some what like this. Suppose there are 3 tables: A,B & C

data in table A:

Element_type_id Element_name
1 CTC(Cost To Company)
2 HRA
3 Medical Allowance
and so on


Data in table B are:

Element_type_id Assignment_number Value
1 50 10000000
1 60 20000000
2 50 5000
2 60 6000
3 50 15000
4 60 12000
and so on


Data in table C is:

Emp_Num Emp_name Assignment_Number
10 Maya 50
20 Chaya 60
30 Kaya 70
and so on.


Now the output that i want is:

Emp_Num CTC HRA Medical Allowance
10 10000000 5000 15000
20 20000000 6000 12000
and so on.

So pls let me know that how can i do using select statement.

Thanks & Regards,
Monika


Re: select query to take row value as column name [message #423732 is a reply to message #423731] Sun, 27 September 2009 09:52 Go to previous message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Post a working Test case: create table and insert statements along with the result you want with these data.


Quote:
What is your client program? You cannot do it in a single SQL statement.


Please read what we post and answer our question.

Regards
Michel
Previous Topic: External procedure error
Next Topic: Please suggest a logic for this
Goto Forum:
  


Current Time: Wed Sep 28 12:34:15 CDT 2016

Total time taken to generate the page: 0.07896 seconds