Grab column name stored in a table then use it to retrieve records.

Question

Post reply

Grab column name stored in a table then use it to retrieve records.

mhg1063

Say Hey Kid

Points: 682
More actions
August 7, 2012 at 7:28 am

#264785

Hi all,
We have a software application that allows our users to group records based on a column which is written to another table. Since it can be a different column for each client and each set of records. They are allowed to store the column used in a table. We need to be able to query the table, select the column name then use it in a join. This seems to work in a stored procedure. I can grab the column in a variable then use dynamic sql to join to a table to retrieve records. Is there a way to do this with a table function. I get the errors can’t use exec in a function. I want to create a function to grab the column then use it to populate the table function.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mhg1063 Say Hey Kid Points: 682 More actions · Answer 1

Records table:

ID | LNAME | FNAME |State Cd

----------|----------|--------- |

0000001 | Bloggs | Joe | OH

0000002 | Smith | John | OH

0000003 | Doe | Jon | MI

Filter table;

Name| Rec col used

---------|--------------

OH | State Cd

I want to grab the “State Cd” column then join to the records table to filter table

Inner join filter filter.name = records.state_cd

sestell1 SSChampion Points: 10230 More actions · Answer 2

You can try using a case statement in your join for each potential column.

SELECT

A.ColumnA1,

B.ColumnB2

FROM

TableA A

Join

TableB B

ON 1 = 1

AND A.ColumnA1 = CASE @ColumnName WHEN 'ColumnB1' THEN B.ColumnB1 ELSE A.ColumnA1 END

AND A.ColumnA2 = CASE @ColumnName WHEN 'ColumnB2' THEN B.ColumnB2 ELSE A.ColumnA2 END

AND A.ColumnA3 = CASE @ColumnName WHEN 'ColumnB3' THEN B.ColumnB3 ELSE A.ColumnA3 END

AND A.ColumnA4 = CASE @ColumnName WHEN 'ColumnB4' THEN B.ColumnB4 ELSE A.ColumnA4 END

...not sure if it will work, but it was my first thought when I read your question.

sestell1 SSChampion Points: 10230 More actions · Answer 3

mhg1063 (8/7/2012)
Records table:
ID | LNAME | FNAME |State Cd
----------|----------|--------- |
0000001 | Bloggs | Joe | OH
0000002 | Smith | John | OH
0000003 | Doe | Jon | MI
Filter table;
Name| Rec col used
---------|--------------
OH | State Cd
I want to grab the “State Cd” column then join to the records table to filter table
Inner join filter filter.name = records.state_cd

Oh, so you aren't dynamically joining on the column, you want to dynamically filter by the specified column. In that case try something like this:

SELECT

A.ColumnA1,

B.ColumnB2

FROM

TableA A

Join

TableB B

ON A.ColumnA1 = B.ColumnB1

WHERE 1 = 1

AND A.ColumnA1 = CASE @ColumnName WHEN 'ColumnA1' THEN @ColumnValue ELSE A.ColumnA1 END

AND A.ColumnA2 = CASE @ColumnName WHEN 'ColumnA2' THEN @ColumnValue ELSE A.ColumnA2 END

AND A.ColumnA3 = CASE @ColumnName WHEN 'ColumnA3' THEN @ColumnValue ELSE A.ColumnA3 END

AND A.ColumnA4 = CASE @ColumnName WHEN 'ColumnA4' THEN @ColumnValue ELSE A.ColumnA4 END