Effect select with dynamic column

Asked

Viewed 2,207 times

1

I have a consultation with N relationships, in one of these related tables I need to select a specific column that follows the pattern mes_valor, where mes is an abbreviation for one of the possible twelve jan_valor, fev_valor, ..._valor.

The SELECT is something like:

SELECT [dbo.mes_valor(MONTH(tabela_relacionada.data_emissao))] as valor FROM tabela

Where from the date of issue, I will obtain the month and select the specific column to obtain the required value. dbo.mes_valor is a simple function just to return the column name:

CREATE FUNCTION dbo.mes_valor (@mes INT)
RETURNS NVARCHAR(MAX)
AS BEGIN
    DECLARE @mes_valor NVARCHAR(MAX)
    SET @mes_valor = (SELECT case @mes
            WHEN 1 THEN 'jan_valor'
            WHEN 2 THEN 'fev_valor'
            WHEN 3 THEN 'mar_valor'
            WHEN 4 THEN 'abr_valor'
            WHEN 5 THEN 'mai_valor'
            WHEN 6 THEN 'jun_valor'
            WHEN 7 THEN 'jul_valor'
            WHEN 8 THEN 'ago_valor'
            WHEN 9 THEN 'stm_valor'
            WHEN 10 THEN 'out_valor'
            WHEN 11 THEN 'nov_valor'
            WHEN 12 THEN 'dez_valor'
        END)
    RETURN @mes_valor
END

While performing the SELECT as demonstrated above, the SQL SERVER interprets as a string, keeping the static query, example:

VALOR
jan_valor
ago_valor
dez_valor

When to return the column value

VALOR
10.3
23.1
55.2
sql-server

  • http://answall.com/questions/144285/pivot-sql-colunas-din%C3%A2micas-com-Vari%C3%A1vel/144313#144313

  • But in your answer is that the value is defined before the consultation, the value I need to pass is tied to the relationship, the column tabela_relacionada.data_emissao.

  • When you call your FUNCTION dbo.mes_value (@mes INT) it will only return this, ie WHEN 1 THEN 'jan_value' instead of its 'jan_value' you would have to select to get the value

1 answer

1


You can’t use a FUNCTION or any other procedure within a select returns a column name of the table or view that you are using, because the return within a select in this case it is already the value of the column, ie when you call your Function it already returns the value for each row of the select as you return a column name would have to run select again so that sql knows that return name would be a table column name and not its value.

There are 3 ways for you to do this.

First;

Change your FUNCTION to return the column value and not the name.

CREATE FUNCTION dbo.mes_valor (@mes INT)
RETURNS numeric
AS BEGIN
    DECLARE @mes_valor numeric
    SET @mes_valor = (SELECT case @mes
            WHEN 1 THEN (SELECT jan_valor FROM tabela) 
            ..........
        END)
    RETURN @mes_valor
END

Second;

Make your case in the select.

SELECT
 case 
      WHEN MONTH(tabela_relacionada.data_emissao) = 1 THEN jan_valor 
 ..........
 end as valor 
FROM tabela

Third;

This would be the most inadvisable, you would have to store your select in a string(varchar) and only then execute the query through the execute();

Something like;

declare @query varchar(max);

set @query = "SELECT  [dbo.mes_valor(MONTH(tabela_relacionada.data_emissao))] as valor FROM tabela";

execute(@query);

See more details here.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.