Make select with dynamic column

Navigation
1

I have a query with N relationships, in one of those related tables I need to select a specific column that follows the mes_valor pattern, where mes is an abbreviation for one of the twelve possible 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 issue date, I'll get the month and select the specific column to get the required amount. 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

When doing SELECT as shown previously, SQL SERVER interprets as a string , holding the static query, eg: 

VALOR
jan_valor
ago_valor
dez_valor

When should I return the column value 

VALOR
10.3
23.1
55.2
    
asked by anonymous 10.08.2016 / 22:11

1 answer

1

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

There are 3 ways you can do this.

First;

Change your FUNCTION to return the value of the column 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 at the select itself. 

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

Third,

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

Something like that; 

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 .

    
10.08.2016 / 22:39
Bring contacts from your smartphone Authenticate in a database and change information in others