Home » SQL & PL/SQL » SQL & PL/SQL » How to join a table column names with data from other table (Oracle 11g,dynamic sql)
How to join a table column names with data from other table [message #561064] Wed, 18 July 2012 11:18 Go to next message
ravidhanek
Messages: 18
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'

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 Go to previous messageGo to next message
BlackSwan
Messages: 22537
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
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
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 Go to previous messageGo to next message
ravidhanek
Messages: 18
Registered: August 2011
Junior Member
It has few other columns which we dont need for sample here.
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 Go to previous messageGo to next message
ravidhanek
Messages: 18
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?
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 Go to previous messageGo to next message
BlackSwan
Messages: 22537
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.
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 Go to previous message
Michel Cadot
Messages: 58625
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
Previous Topic: Easy query
Next Topic: PL/SQL Issue
Goto Forum:
  


Current Time: Wed Jul 30 15:51:56 CDT 2014

Total time taken to generate the page: 0.12329 seconds