Select Column name dynamically based on rows from another table

Question

Hello Helper,

I am having trouble joining 2 tables.

Table Names: QUESTION ANSWER

QUESTION Table contains a column with column name as ANSWER_LOCATION and it is INT data type. This column contains number of rows with values (1, 2, 3, 4 ...) and these numbers are column names in ANSWER table.

Example:

select QUESTION.ANSWER_LOCATION from QUESTION where ID = 5

The output of this query will be 5 (scalar value) and I need to join value (5) with ANSWER table to select the COLUMN5.

Please let me know if I need to provide more information.

Thanks in anticipation.

Cheers, D

Answer 1

0

DECLARE @AnswerLocation int, @SQL varchar(MAX)
SELECT @AnswerLocation = ANSWER_LOCATION from QUESTION where ID = 5
SET @SQL = 'SELECT COLUMN' + convert(varchar, @AnswerLocation) + ' FROM ANSWER'
EXEC (@SQL)

However, you should really consider re-designing this data set, such that you have the same value in the ANSWER table, then you can enforce referential integrity with foreign keys etc. It would be worth posting the DDL in a separate question titled something along the lines of 'how should I re-design these tables'.

answered Mar 24 '10 at 05:58 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

+1 Good answer and excellent suggestion.

Mar 25 '10 at 12:01 AM Tom Staab

Thank you very Matt. Intially I thought I can use dynamic SQL. However some of my SQL friends scared me of saying Dynamic SQL might cause SQL Injections.

Just wanted to clarify one more question.... Can I integrate Dynamic SQL in UDF's? Because the final requirement is to create UDF's based the above 2 tables.

Once thank you very much for your suggestion. Much appreciated.

I am also posting a seperate question for redisgning this table.

Cheers, DV

Mar 26 '10 at 02:48 AM OnlyGraphite

(comments are locked)

Select Column name dynamically based on rows from another table

Follow this question

Related Questions