"Christopher" <Christop***@discussions.microsoft.com> wrote in message
news:A1B53D3E-F7DC-43C0-A2A7-857F27A9D82B@microsoft.com...
> Hi there, I'm currently querying the team foundation server database in
> SQL
> Reporting Services.  In one table there are two columns for example, a
> field
> name and a field ID.  An example of one row is below...
>
> FieldName      FieldID
> Assigned To   Fld10135
>
> In another table, there are columns corresponding to the Field IDs as in
> the
> below example.
>
> Work Item  Fld10135
> Test 1        Jerry
>
> I am trying to write a query to select all the items where the Assigned To
> field is equal to 'Jerry'.  I need to reference the field however using
> the
> Field Name, not the ID.  So I am needing to get the Field ID from the
> first
> table and use that value as a column name in the second table.  Below are
> some tries I've attempted.
>
> SELECT (SELECT FROM Fields WHERE (ReferenceName = 'AssignedTo')) FROM
> WorkItems
>
> Obviously this doesn't work but my goal is to use the results of the first
> subquery in the outermost query.  Subqueries seem to work in the opposite
> direction, using the results of an outer query in the inner one.  Any help
> on
> this would be greatly appreciated, thanks so much!
>
> Christopher
>
>
>
>
>
>

"Christopher" wrote:

> Hi there, I'm currently querying the team foundation server database in SQL
> Reporting Services.  In one table there are two columns for example, a field
> name and a field ID.  An example of one row is below...
>
> FieldName      FieldID
> Assigned To   Fld10135
>
> In another table, there are columns corresponding to the Field IDs as in the
> below example.
>
> Work Item  Fld10135
> Test 1        Jerry
>
> I am trying to write a query to select all the items where the Assigned To
> field is equal to 'Jerry'.  I need to reference the field however using the
> Field Name, not the ID.  So I am needing to get the Field ID from the first
> table and use that value as a column name in the second table.  Below are
> some tries I've attempted.
>
> SELECT (SELECT FROM Fields WHERE (ReferenceName = 'AssignedTo')) FROM
> WorkItems
>
> Obviously this doesn't work but my goal is to use the results of the first
> subquery in the outermost query.  Subqueries seem to work in the opposite
> direction, using the results of an outer query in the inner one.  Any help on
> this would be greatly appreciated, thanks so much!
>
> Christopher
>
>
>
>
>
>

"Alejandro Mesa" wrote:

> Christopher,
>
> I am afraid that your option will be to use dynamic sql.
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
>
> declare @sql nvarchar(4000)
> declare @c sysname
>
> set @c = (select FieldID from dbo.t1)
>
> if @c is not null
> begin
>    set @sql = N'select * from dbo.t2 where [' + @c + '] = @v'
>    exec sp_executesql @sql, N'@v varchar(25)', 'Jerry'
> end
> go
>
>
> AMB
>
> "Christopher" wrote:
>
> > Hi there, I'm currently querying the team foundation server database in SQL
> > Reporting Services.  In one table there are two columns for example, a field
> > name and a field ID.  An example of one row is below...
> >
> > FieldName      FieldID
> > Assigned To   Fld10135
> >
> > In another table, there are columns corresponding to the Field IDs as in the
> > below example.
> >
> > Work Item  Fld10135
> > Test 1        Jerry
> >
> > I am trying to write a query to select all the items where the Assigned To
> > field is equal to 'Jerry'.  I need to reference the field however using the
> > Field Name, not the ID.  So I am needing to get the Field ID from the first
> > table and use that value as a column name in the second table.  Below are
> > some tries I've attempted.
> >
> > SELECT (SELECT FROM Fields WHERE (ReferenceName = 'AssignedTo')) FROM
> > WorkItems
> >
> > Obviously this doesn't work but my goal is to use the results of the first
> > subquery in the outermost query.  Subqueries seem to work in the opposite
> > direction, using the results of an outer query in the inner one.  Any help on
> > this would be greatly appreciated, thanks so much!
> >
> > Christopher
> >
> >
> >
> >
> >
> >