joining on cell value = column name?

[TimeTraveler]

I'm used to working on a non-SQL legacy database product that used boolean and scalar question codes. One of its features was that it was possible to make really complex reports very easily, once the database's query syntax was learned. (I guess that goes without saying, most things are easy once learned...)

Now I'm working in SQL, and while I can do the basics in SQL (DML, et al), I'm having problems in one aspect:

I want to join two tables where the cell value in one table equals the column name in another table.

The reason is that my data comes in like this:

description: id, item1, item2, item3, item4...
1001,1,0,1,....
1002,0,0,1,....
1003,1,0,0,

I do not want to transform the data to be this:

description: id, item, yes/no
1001,item1,1
1001,item2,0
1001,item3,1
...
1002,item1,0
1002,item2,0
1002,item3,0
...

There has to be an easy way to connect cell values to either table names and or column names of a table.

An example of how to do that would be soooooo sweet.

My preliminary search on this forum hasn't yielded anything. I've also looked through my Oracle 9i SQL book and SQL Unleashed and didn't find it. Maybe I'm being dense, and if so I appologize.

Thanks in advance.

Sean

PS - The examples below are for illustration, so any SQL statement that hard-wires the column name to q1 items won't work b/c the number of items in the q1 table are anywhere from 20-1000. The data is typical "wider" than it is "deep", and sparsely populated, which is why it gives spreadsheets and typical databases fits.

table: q1
header: id, 1, 2, 3, 4, 5, 6, 7, 8
1001,1,0,1,0,0,0,0,0
1002,1,0,0,0,0,0,0,1
1003,0,0,1,0,0,0,0,0
1004,1,0,0,0,0,0,0,0

table: list1
header: cid, name
1,red
2,green
3,blue
4,violet
5,yellow
6,brown
7,orange
8,lime

table: resp
header: respid,respname
1001, Johnny
1002, Sally
1003, Nancy
1004, Billy

Sample Report
Filter: Names ending in 'y'
-------------
Color  Likes?
red    75%
green  50
blue    0

 

[LKBrwnDBA]

It is not clear what is your requirement.

Have you coded anything yet?


BTW: The result from your example should be:

Sample Report
Filter: Names ending in 'y'
-------------
Color  Likes?
red    75%
green   0
blue   50
lime   25

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[TimeTraveler]

Right about the result. But you (and hopefully others) got the idea about the output I'm seeking.

The requirement is that the SQL statement address the name of the column in the answer table.

Or rather, the column names in q1 link to the values in list1.cid. I really can't figure out how to 'attach' this in SQL.

Sean

 

[juliane26]

seems like you have a similar problem like in

http://www.tek-tips.com/viewthread.cfm?qid=1099074&page=1

either ask the question again in your dbms related forum, or check on your DBMS internal information views and schema
DB2: system catalog: SYSCAT.COLUMNS
ORACLE: Data Dictionary: COL
...


Juliane

 

[TimeTraveler]

What is the SQL for MS Access to list the names of columns in a table?

 

[r937]

What is the SQL for MS Access to list the names of columns in a table?

i expect you will get an answer to this more readily in one of the Access forums

r937.com | rudy.ca