Column name with text and column name

• pedroccamara

  SSC-Addicted

  Points: 431

  More actions

  January 13, 2023 at 10:55 am

  #4137327

  Hello all. I hope everyone's ok

  I have this query:

  SELECT  cont, type, month01, month02, month03...

  FROM acum

  Where year=2022

  I'd like that for each month it shows a text and the year, like Jan-2022, Feb-2022 and so on.

  Something like

  SELECT  cont, type, month01 AS CONCAT( 'Jan-',year) , month02 AS CONCAT( 'Feb-',year) ...

  FROM acum

  Where year=2022

  I tried with concat, with the '+' but it didn't work.

  Can any of you help?

  Thanks in advance

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  January 13, 2023 at 11:19 am

  #4137340

  You'll have to use dynamic SQL for this.

  

• pedroccamara

  SSC-Addicted

  Points: 431

  More actions

  January 13, 2023 at 11:33 am

  #4137344

  I'm sorry. I don't even know what that means. Can you give an example?

  What I want is a column name with text and field name like my example above

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  January 13, 2023 at 12:11 pm

  #4137355

  Here is some dynamic SQL to give you an idea. You cannot 'inject' variables into column aliases using 'normal' T-SQL.

  DECLARE @Yr SMALLINT = 2022;
  DECLARE @Sql VARCHAR(MAX)
      = 'SELECT  cont, type, month01 AS [Jan-@year] , month02 AS [Feb-@year] FROM acum Where year = @year';
  
  SET @Sql = REPLACE (@Sql, '@year', @Yr);
  
  EXEC(@Sql);

  ---

• pedroccamara

  SSC-Addicted

  Points: 431

  More actions

  January 13, 2023 at 12:15 pm

  #4137357

  Thank you so much for your help Phil