views:

16

answers:

3

Is it possible to create a view (not stored procedure) with dynamic column names based on another table? For example:

Code:
CodeId|Description
------------------
     1|Title
     2|Notes

Data:
DataId|Content|CodeId|GroupId
-----------------------------
     1|Title1 |     1|      1
     2|Note1  |     2|      1
     3|Title2 |     1|      2
     4|Note2  |     2|      2

Select Result:
GroupId|Title |Notes
-------------------
      1|Title1|Note1
      2|Title2|Note2

The column names "Title" and "Notes" would come from the Code table. I'm guessing the answer is no, but would like to confirm. Thanks!

Edit: I understand how this could be "dangerous". If someone updates the code description the view would change, breaking any SQL dependent on the column names. In that case I could use the CodeId instead, which would not be allowed to change.

+1  A: 

I was thinking, worse case, you could do something where the first row returned had all your column headers. Would be a bit tricky, and you'd probably have to suffer the performance hit for a UNION, but sounds doable.

mattruma
Interesting, I hadn't thought of that. I was also thinking that if the CodeId isn't allowed to change (due to difficulty with dependent queries) then really, I already know what the columns are going to be ahead of time. In that case I could do a bunch of subselects for each column or use a CTE for the group and a bunch of joins for each column. I'm not sure the pain the dynamic columns could bring make up for not specifying each column.
Nelson
+1  A: 

You could write a program in Java or C or whatever that dynamically creates the "create view" statement using values from a database and then executes it.

If you're looking for a way to do it with only SQL statements, I agree that the answer is probably "can't be done" but I'm reluctant to make such statements as people are always coming up with clever ways to do something that I never thought of.

Jay
It would probably take me longer to write a program to create the view, unless I had a lot of columns and a lot of similar views to create. :) I can think of a lot of reasons this would be a bad idea if it were possible. We'll see if there's someone more clever than us.
Nelson
If you have two or three views you want to create, sure, I'd just create the views by hand. If you have a hundred, or if the set of columns is not known to you, I'd write the program. Can't say I've ever had a need to do such a thing.
Jay
A: 

The perils of the EAV (Entity-Attribute-Value) model are many, and you're just setting yourself up for a ton of headaches in the future. With that said, your specific question seems possible to solve to me. You've been warned though...

You could do this by putting a trigger on your code table. Any time that someone added, deleted, or updated one of the rows in the table the trigger would be responsible for recreating the view with the correct statement.

Tom H.
I completely agree there are many perils. I think the trigger+recreate view idea is the closest to a "good" answer.
Nelson

related questions