OraFAQ Forum: SQL & PL/SQL » How to join a table column names with data from other table

Home » SQL & PL/SQL » SQL & PL/SQL » How to join a table column names with data from other table (Oracle 11g,dynamic sql)

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

How to join a table column names with data from other table [message #561064]

Wed, 18 July 2012 11:18

ravidhanek
Messages: 12
Registered: August 2011

Junior Member

Hi,

I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.


CREATE TABLE SALESHIST
(
  PRODUCT  VARCHAR2(30 BYTE),
  Q1       VARCHAR2(30),
  Q2       VARCHAR2(30),
  Q3       VARCHAR2(30),
  Q4       VARCHAR2(30)
)
 
Insert into SALESHIST
   (PRODUCT, Q1, Q2, Q3, Q4)
 Values
   ('Oracle EE', 100, 'AAA', 130, 128);
 
 
 
 CREATE TABLE SALES
(
  REGION       VARCHAR2(10)
  )
 
INSERT INTO SALES values('Q1');
INSERT INTO SALES values('Q2');
 
Desired output :
product       column2   column3
'Oracle EE'   'Q1=100'  'Q2=AAA'

Report message to a moderator

Re: How to join a table column names with data from other table [message #561070 is a reply to message #561064]

Wed, 18 July 2012 11:43

BlackSwan
Messages: 20085
Registered: January 2009

Senior Member

> CREATE TABLE SALES ( REGION VARCHAR2(10) );
a single column table is not Normalized.

seeing as how the whole design is flawed, I am not surprised.

Report message to a moderator

Re: How to join a table column names with data from other table [message #561071 is a reply to message #561064]

Wed, 18 July 2012 11:44

Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Very bad design, it I understand it, you have column names of table in the data of another table.
It should be better to change the design then to try to write this query.
I don't see any case when this is a valid design, can you explain your case?

In addition, why not the following result?

product       column2   column3
'Oracle EE'   'Q2=AAA'  'Q1=100'

Regards
Michel

Report message to a moderator

Re: How to join a table column names with data from other table [message #561072 is a reply to message #561064]

Wed, 18 July 2012 11:52

ravidhanek
Messages: 12
Registered: August 2011

Junior Member

It has few other columns which we dont need for sample here.

Report message to a moderator

Re: How to join a table column names with data from other table [message #561073 is a reply to message #561070]

Wed, 18 July 2012 11:56

ravidhanek
Messages: 12
Registered: August 2011

Junior Member

This scenario is valid in my case. We are writing the code to make it work for future changes as well. It might look awkward, But it is very possible in our system. I work on meta data for products. currently i have only 4 columns to consider as meta data. they might increase to 7 or 8. We are planning to code for up to 15. Does this make sense?

Report message to a moderator

Re: How to join a table column names with data from other table [message #561076 is a reply to message #561073]

Wed, 18 July 2012 12:16

BlackSwan
Messages: 20085
Registered: January 2009

Senior Member

>This scenario is valid in my case.
If you say so.
>Does this make sense?
does not make sense to me.

SQL must be static at compile time.
You are restricted & constrained by the reality stated above.

Report message to a moderator

Re: How to join a table column names with data from other table [message #561078 is a reply to message #561076]

Wed, 18 July 2012 13:07

Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As Blackswan said, it does not make sense.
There should NOT be 4, 5... 15 columns, there should be 4, 5... 15 ROWS.
And you didn't answer to my last question.

Regards
Michel

Report message to a moderator

Previous Topic:	Easy query
Next Topic:	PL/SQL Issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 18:31:31 CDT 2013

Total time taken to generate the page: 0.12390 seconds