Pfthb

I am trying to get a cell value from another table (UserTable) for which the column of the cell is specified in the original table (DataTable). The cell value is going to be used to calculate a score which will then determine the ordering of the query results.

UserTable:
+--------+------+------+------+
| userid | c1 | c2 | c3 |
+--------+------+------+------+
| id1 | 0 | 1 | 1 |
| id2 | 0 | 0 | 1 |
| id3 | 1 | 0 | 0 |
| id4 | 1 | 1 | 0 |
+--------+------+------+------+

DataTable:
+--------+------+------+------+
| id | ColA | ColB | ColC |
+--------+------+------+------+
| 1 | A | B | c1 |
| 2 | D | E | c3 |
| 3 | G | H | c2 |
| 4 | J | K | c3 |
+--------+------+------+------+

My current, not working, query is:

SELECT *, 
 (SELECT DataTable.ColC 
 FROM UserTable 
 WHERE UserTable.userid = 'some_user_id') AS score 
FROM DataTable 
ORDER BY score DESC

But this doesn't select the appropriate column in UserTable and instead returns the string value contained in ColC.

The expected output:
If 'some_user_id' was set to 'id2' for example. The the expected output would be:

+--------+------+------+------+------+
| id | ColA | ColB | ColC | score|
+--------+------+------+------+------+
| 2 | D | E | c3 | 1 |
| 4 | J | K | c3 | 1 |
| 1 | A | B | c1 | 0 |
| 3 | G | H | c2 | 0 |
+--------+------+------+------+------+

where the score column is simply the lookup of the value from the UserTable.

Is it possible to solve this problem?

You cannot do this dynamically. But you can build a CASE statement that maps column names to columns:

SELECT DataTable.*, (
 SELECT CASE DataTable.ColC
 WHEN 'c1' THEN UserTable.c1
 WHEN 'c2' THEN UserTable.c3
 WHEN 'c3' THEN UserTable.c3
 END
 FROM UserTable
 WHERE UserTable.userid = 'id2'
) AS score
FROM DataTable
ORDER BY score DESC

Declare @expr nvarchar(max); 
Declare @value nvarchar(max); 
DECLARE db_cursor CURSOR 
FOR Select colC from dbo.DataTable;

OPEN db_cursor; 
FETCH NEXT FROM db_cursor INTO @value; 
Set @expr = 'Select ' + @value; 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 Set @expr += ',' + @value 
 FETCH NEXT FROM db_cursor INTO @value; 
END; 
CLOSE db_cursor; 
DEALLOCATE db_cursor; 

EXEC (@expr + ' from dbo.UserTable')

I like to express this by unpivoting the user table:

select dt.*, uts.score
from datatable dt left join
 ((select ut.userid, 'c1' as which, c1 as score
 from usertable ut
 ) union all
 (select ut.userid, 'c2' as which, c2 as score
 from usertable ut
 ) union all
 (select ut.userid, 'c3' as which, c3 as score
 from usertable ut
 ) 
 ) uts
 on dt.colc = uts.which 
where uts.userid = ?;

The reason I prefer this approach is because the subquery suggests the table that you really need. You should not be storing the different score values in columns. You should have a userScores table with one row per user and per score.