Dynamic column names

Question

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

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” will come from the Code table. I suppose the answer is no, but I would like to confirm. Thanks!

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

Answer 1

There are many dangers of the EAV (Entity-Attribute-Value) model, and you just set yourself up for a ton of headaches in the future. With that said, your specific question seems possible to solve for me. You were warned though ...

You can do this by placing a trigger in your code table. Each time someone added, deleted, or updated one of the rows in the table, the trigger will be responsible for re-creating the view with the correct statement.

Answer 2

I thought the worst case is, you could do something where the first row returned, had all the column headers. It would be a little tricky, and you might have to endure the performance hit for UNION, but it sounds doable.

Answer 3

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

If you are looking for a way to do this only with SQL statements, I agree that the answer is probably “cannot be executed,” but I’m reluctant to make statements like people always come up with smart ways to do things that I’m never Did not think.