OraFAQ Forum: SQL & PL/SQL » select query to take row value as column name

Home » SQL & PL/SQL » SQL & PL/SQL » select query to take row value as column name

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

select query to take row value as column name [message #423727]

Sun, 27 September 2009 08:42

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

Report message to a moderator

Re: select query to take row value as column name [message #423729 is a reply to message #423727]

Sun, 27 September 2009 09:03

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: select query to take row value as column name [message #423732 is a reply to message #423731]

Sun, 27 September 2009 09:52

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	External procedure error
Next Topic:	Please suggest a logic for this

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 24 19:03:15 CDT 2025