Create column name based on value without execute

Question

I need to create a column name based on the value of other columns. I need to return a value from a column, but the specific name depends on the value insert on other table.

From intance:

Table A

Column1 | Column2
1         2

Base on that values I need to go to the table B to the column "VE12".

I need this dynamiclly, so the execute(@query) is my last option and I would like to avoid CASE WHEN statments because I have more than 50 options.

My query will be something like:

select case when fn.tab=8 and fo.pais=3 then cp.ve83 end 
FROM fn
INNER JOIN fo ON fo.stamp = fn.stamp
INNER JOIN cp

If the value in the column tab is 8 and the value in column pais is 3 I should return the value in column ve83.

Thanks for all the help!

Could you prvoide some sample data and expect result?

D-Shih
– D-Shih

2018-07-30 11:34:21 +00:00
Commented Jul 30, 2018 at 11:34 — D-Shih
– D-Shih, Commented Jul 30, 2018 at 11:34
Hi D-Shih, I change the question it more information.

Ricardo Castro
– Ricardo Castro

2018-07-30 11:48:32 +00:00
Commented Jul 30, 2018 at 11:48 — Ricardo Castro
– Ricardo Castro, Commented Jul 30, 2018 at 11:48
You need to use dynamic SQL

D-Shih
– D-Shih

2018-07-30 12:52:39 +00:00
Commented Jul 30, 2018 at 12:52 — D-Shih
– D-Shih, Commented Jul 30, 2018 at 12:52

Erwin Smout · Accepted Answer · 2018-07-30 14:00:24Z

3

The only sensible option is to go back to the business meaning of the data and redesign the database according to that, instead of according to "technique-oriented abstractions" such as these that SQL was never intended to support.

The main reason for this is that SQL was founded on FIRST order logic, and this precludes supporting stuff like varying domains. Which you are doing (or at least seeking to do) because ve12 could be a DATETIME and ve83 could be a VARCHAR and ve56 coulb be a BLOB etc. etc. So there is just no way for you [or anyone else] to determine the data type of the results in your query, and it is even more impossible to attach meaning to what comes out of your desired query precisely because of this varying-domain and varying-source characteristic.

answered Jul 30, 2018 at 14:00

Erwin Smout

18.5k4 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create column name based on value without execute

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related