I have a table with 10 columns as col_1,col_2,.... col_10. I want to write a select statement that will select a value of one of the row and from one of these 10 columns. I have a variable that will decide which column to select from. Can such query be written where the column name is dynamically decided from a variable.
Add a comment
|
8 Answers
Yes, using a CASE statement:
SELECT CASE @MyVariable
WHEN 1 THEN [Col_1]
WHEN 2 THEN [Col_2]
...
WHEN 10 THEN [Col_10]
END
Whether this is a good idea is another question entirely. You should use better names than Col_1, Col_2, etc.
You could also use a string substitution method, as suggested by others. However, that is an option of last resort because it can open up your code to sql injection attacks.
Comments
Sounds like a bad, denormalized design to me.
I think a better one would have the table as parent, with rows that contain a foreign key to a separate child table that contains ten rows, one for each of those columns you have now. Let the parent table set the foreign key according to that magic value when the row is inserted or updated in the parent table.
If the child table is fairly static, this will work.
3 Comments
Craig
What it sounds like is a microsoft (e.g. Sharepoint) database.
duffymo
It's tagged as sqlserver and sqlserver2005, so I assumed relational database.
ConcernedOfTunbridgeWells
he OP may not have any control over the schema. Packaged applications often use this type of structure for user defined fields that are used differently depending on how the software is configured. Although it looks inelegant, the alternatives are often worse. For a comparison, try developing an ETL process to stage data from an insurance system that stores its whole policy document as an XML blob in an ntext column.
Since I don't have enough details, I can't give code. Instead, I'll explain.
Declare a string variable, something like:
declare @sql varchar(5000)
Set that variable to be the completed SQL string you want (as a string, and not actually querying... so you embed the row-name you want using string concatenation).
Then call: exec(@sql)
All set.
3 Comments
Joel Coehoorn
Make sure you're taking appropriate measures to prevent sql injection. If the user can enter arbitrary text for the column name then you'll be in big trouble.
Jason Kester
This is a good illustration of why it's a bad idea to let the questioner decide which answer is "correct". The correct answer (with tons of points, below) is that you don't need dynamic sql for this particular case. If you did need it though, then this would be the right answer.
user54650
I disagree. This is a good illustration that popularity contexts are biased. The requester was asking about Dynamic queries and beyond that, we are not aware of the entirity of what the requester actually wanted it for. Proof positive that the correct answer isn't always the right answer.
I assume you are running purely within Transact-SQL. What you'll need to do is dynamically create the SQL statement with your variable as the column name and use the EXECUTE command to run it. For example:
EXECUTE('select ' + @myColumn + ' from MyTable')
IMHO, Joel Coehoorn's case statement is probably the best idea
... but if you really have to use dynamic SQL, you can do it with sp_executeSQL()
Comments
I have no idea what platform you are using but you can use Dynamic LINQ pretty easily to do this.
var query = context.Table
.Where( t => t.Id == row_id )
.Select( "Col_" + column_id );
IEnumerator enumerator = query.GetEnumerator();
enumerator.MoveNext();
object columnValue = enumerator.Current;
Presumably, you'll know which actual type to cast this to depending on the column. The nice thing about this is you get the parameterized query for free, protecting you against SQL injection attacks.
Comments
This isn't something you should ever need to do if your database is correctly designed. I'd revisit the design of that element of the schema to remove the need to do this.
Comments
