How can i create a view in SQL Server,where columns are defined and appear according to a value stored in another table.These columns are almost identically calculated by a function but their number is set from another table's record.
2 Answers
This is not possible with a view because SQL Server queries are always statically typed in the sense that both column count, names and types are statically known at execution time.
You need dynamic SQL for dynamic columns.
Views do not support dynamic SQL. You have to find some other way of returning the data, maybe with one row per logical column.
2 Comments
Aniket Inge
can you explain the last step with an example so it seems clear?
usr
Example: You want to return one column per month. As this is not possible, you return one row per month. Instead of (Jan, Feb, Mar, ...) you return (Jan), (Feb), (Mar) from the view.
You can create an inline Table-valued function and define your fields and return the table then call the function inside your view.
1 Comment
usr
Is there a way to make an inline TVF have a dynamic number of columns? I think the same restrictions as for views apply in this regard.