Concatenating Column Names

• Justyna

  Mr or Mrs. 500

  Points: 594

  More actions

  February 22, 2006 at 4:06 pm

  #167237

  I am having some trouble with column names and I was hoping someone could help me out.

  This is my statement:

  Update neda_new

      set +[cost break ]+ ' + @myNewID + ' = [Cost Break],

      [Value Cost ]+ ' + @myNewID + ' = [Value Cost],

      [Msr Value ]+ ' + @myNewID + ' = [Msr Value]

      where [part number] =  @partnumber

      and [myNewId] = @myNewId

      from neda_newark_pricing

  Basically I want to update the column that has the name of ie: 'cost break' plus the value of my variable.

  My table neda_new has the following columns

  [Part Number]

  [Cost Break 1]

  [Value cost 1]

  [Msr Value 1]

  [Cost Break 2]

  [Value Cost 2]

  etc.

  Any suggestions on the correct syntax (if it is even possible) for this?

  Thanks a million.

  J

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  February 22, 2006 at 4:18 pm

  #622785

  You need dynamic SQL for that. But that isn't the real problem.

  Do you have control over the design of this table ? If yes, you should consider normalizing it and removing the repeating groups.

   

• Justyna

  Mr or Mrs. 500

  Points: 594

  More actions

  February 22, 2006 at 4:22 pm

  #622787

  PW: Thank you for the response.  I'm not really sure what you mean though.  The above sql statement is part of a larger query involving cursors.  I do have the ability to change the design of the table, but I actually need all those column names the way they are.

  My original table has data in it like this:

  Part Number    Cost Break       Cost Value

  xyz                1                    24.00

  xyz               5                      23.00

  And I need it to all be on 1 line for each part number.

  Anyway, thanks again.

  Justyna

• Chip Cary

  Grasshopper

  Points: 12

  More actions

  February 22, 2006 at 4:26 pm

  #622788

  You will need to use dynamic sql statements to do this.

  something like

  DECLARE @sql varchar(500)

  SET @sql = 'SELECT * FROM ' + @tablename + ' WHERE ID = ' + @MyID

  EXEC(@SQL)

  or

  DECLARE @sql varchar(500)

  SET @sql = 'UPDATE ' + @tablename + ' SET [MyTextField] = ''' + @MyTextValue + ''' WHERE ID = ' + @MyID

  EXEC(@SQL)

  Also you will need to cast/convert the value columns to character data type, if they aren't already, to concatenate properly.

   

• Justyna

  Mr or Mrs. 500

  Points: 594

  More actions

  February 22, 2006 at 4:33 pm

  #622789

  Chip: That's just what I was beginning to realize!  Thanks for the suggestion.  I'll give it a go first thing tomorrow.

  Regards,

  Justyna