动态列名
是否可以基于另一个表创建具有动态列名称的视图（不是存储过程）？例如：
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
列名“Title”和“Notes”将来自 Code 表。我猜答案是否定的，但想确认一下。谢谢！
编辑：我明白这可能是“危险的”。如果有人更新代码描述，视图就会改变，从而破坏任何依赖于列名的 SQL。在这种情况下，我可以使用 CodeId，但不允许更改。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
EAV（实体-属性-值）模型的危险有很多，而且您将来只会遇到很多令人头疼的问题。话虽如此，你的具体问题对我来说似乎可以解决。不过，您已被警告...
您可以通过在代码表上放置触发器来做到这一点。每当有人添加、删除或更新表中的一行时，触发器将负责使用正确的语句重新创建视图。
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.
我在想，最糟糕的情况是，您可以做一些事情，返回的第一行包含所有列标题。会有点棘手，而且您可能不得不承受 UNION 的性能损失，但听起来可行。
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.
您可以用 Java 或 C 或其他语言编写一个程序，使用数据库中的值动态创建“创建视图”语句，然后执行它。
如果您正在寻找一种仅使用 SQL 语句来完成此操作的方法，我同意答案可能是“无法完成”，但我不愿意做出这样的陈述，因为人们总是想出聪明的方法来做到这一点我从未想过的事情。
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.