March 9, 2011 at 7:37 am
I have a table (table A) that has around 200 columns of mixed data. The column names are cryptic. I have a second table (table B) that defines what each column in table A is (format, display name, etc.). I would like to display this data from a stored procedure that returns a horizontal view of table A but the column names displayed in the view are from the Display_Name column of table B. I'm not sure how to do this. I thought about dynamically building the query with aliases in a stored procedure using sqlexecute but I think the string will be too large and will exceed the max size of the nvarchar string which is think is 4000.
Thanks,
Al
March 9, 2011 at 8:39 am
So for each column in a 200 column table, you need to get a row from a different table in order to understand what that column actually is? Yikes. That is one seriously frightening design.
As I see it, you're looking at 200 correlated queries... not pretty, or 200 joins... just as not pretty, but neither of those will work on aliasing the columns
You could try using a GROUP BY and CASE statements... no, I suppose that won't work either... And you can't use synonyms to recreate columns or within aggregate functions...
I think dynamic SQL is the only way to go. I'd say try it and see how close to the edge you get or if you go past it. Use aliases where you can within the dynamic sql to keep the size down....
Sorry, I'm just not sure. You might want to reassess the approach
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply