SQL Select only columns that exists as rows in separate meta data table

Question

Hi

In my app, any user can configure which columns to show, reorder and resize columns. To save every user setting, I have a few tables for that.

One of them is called UserColumns, and looks like this:

Id | UserId | ColumnName | DefaultWidth | Width | SortOrder

Is there any way, without dynamically generating the select string and executing it, that I can select columns from a table, but only the columns that exists in the UserColumns table for that specific user?

So a table like ApplicationErrors:

Id | ExceptionMessage | StackTrace | LogDate

The user with id = 1, have got these columns selected: StackTrace, LogDate.

So I want to get a resultset with only StackTrace and LogDate present, ordered by the value of the SortOrder column from the UserColumns table.

Is this possible?

Answer 1

In order to do the select with unknown columns you will need to dynamically generate the query and execute it.

If you have only a few users then you can create a view, and use create a table that links a view to a user, and you may be able to select based on the view, but, then your application will want to do that, as it can dynamically create a query with an variable table.