Select field value according to field name stored in another field

Question

What I need to do is a calculated column according to values returned by another field:

Supose:

declare @t TABLE (
    code varchar(5), 
    address varchar(20), 
    result_type varchar(1), 
    result varchar(50) 
)

insert into @t values ('0001', '', 'L', 'DFK-2020')
insert into @t values ('0001', '', 'F', 'code')
insert into @t values ('0001', '214, Samuel St', 'F', 'address')

What I need is a select statement returning:

DFK-2020
0001
214, Samuel St.

The only thing I can think of is:

select 
case 
    when result_type = 'L' then result
    when result_type = 'F' then (result) -- -> ????? 
end as ret_values

I got lost in ????. I have to select a field according to what is stored in another field

Something like: "When 'result_type' is 'F', please select the value stored in a field which name is stored in 'result' "

in other languages/scenarios I used to achieve this with "macro references".Something like:

select &(result) from ...

How can I achieve that in sql server 2008 ?

How do you plan on getting all of the data to have a single data type? A single column's data type cannot vary from one row to the next. fyi: If you plan on using dynamic SQL then study up on [SQL Injection](http://bobby-tables.com/). — HABO, Oct 21 '16 at 14:12

score 1 · Answer 1 · answered Oct 21 '16 at 13:36

1

One way:

select *,
    case result_type
        when 'L' then result
        when 'F' then
            case result 
                when 'code' then code
                when 'address' then address
            end
        end
from @t

answered Oct 21 '16 at 13:36

Alex K.

171,639
30
264
288

For small tables is great, but what about hundred possible fields? – AndreaTaroni86 Nov 25 '21 at 17:29

score 0 · Answer 2 · answered Oct 21 '16 at 13:29

You can use a select of subselects, pay attention that this is not the best thing in perfomance, so if you have a big amount of data, it will be heavy.

something like this:

select 
   (select result from t where result_type = 'L') as fieldName1,
   (select result from t where result_type = 'F') as fieldName2,
   .
   .
   .

score 0 · Answer 3 · answered Oct 21 '16 at 13:40

0

SELECT
    CASE result_type
        WHEN 'L' THEN result
        WHEN 'F' THEN 
            CASE result
                WHEN 'code' THEN code
                WHEN 'address' THEN address
                -- and so on
            END
    END AS ret_values

answered Oct 21 '16 at 13:40

TT.

15,774
6
47
88

Problem is that table has more than 150 fields – user3770963 Oct 21 '16 at 14:07
@user3770963 Then you'll have to write 150 `WHEN ... ` parts. No other way than to do that. – TT. Oct 21 '16 at 14:09
@user3770963 You can however turn this into a dynamic query by inspecting the table definition and creating a dynamic query based on the results. See [here](http://stackoverflow.com/a/1054988/243373) for how to get the column names for a table. To create a dynamic query, build the query in a `NVARCHAR(MAX)` (say @query) and execute it using [`EXEC sp_executesql @sql`](https://msdn.microsoft.com/en-us/library/ms188001.aspx) – TT. Oct 21 '16 at 14:10
@user3770963 With the answer as outlined and the pointers in comments, do you think you'll be able to write that? – TT. Oct 21 '16 at 14:20

Select field value according to field name stored in another field

3 Answers3