Concatenate with use of Case or If

• laneke

  Newbie

  Points: 1

  More actions

  November 24, 2008 at 9:23 am

  #128844

  I have 7 fields that I need to concatenate into one, but I need to convert 0 and -1 to actual words during the concatenation. Is there a way to do this in one swoop? I've been able to move the values over for all seven fields using case statements, then go back and update afterwards to concatenate into one.

  Example of the case statements:

  SELECT CASE C_FIRST_AID WHEN -1 THEN '- First Aid' END AS FIRST_AID, CASE C_NON_RECORDABLE WHEN -1 THEN '- Non-Recordable' END AS NON_REC

  But instead of the values being read into separate fields I'd like to pull them into one to where it would read "- First Aid - Non-Recordable" when both fields are -1.

  Any advice? Using Case is not a requirement.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 741475

  More actions

  November 24, 2008 at 9:29 am

  #902576

  Just use the concatenation operator, +

  declare @i int, @j-2 int

  select @i = 0, @j-2 = 0

  select

  case when @i = 1 then 'Test'

  else 'Live'

  end

  +

  case when @j-2 = 0 then ' - First Aid'

  else ' = something' end

  +

  case when @i + @j-2 = 0 then ' - End'

  else ' - The End'

  end