Concatenate fields in Dynamic sql

• RockingAdmin

  SSCrazy

  Points: 2828

  More actions

  May 20, 2010 at 1:05 pm

  #221279

  hi

  i got a problem while concating fileds in a dynamic query

  ex:

  decalre @sql nvarchar(2000)

  set @sql='select col1'+''+'col2'+''+'col3'+''+'col4 from table'

  exec(sql)

  i am getting errors while concatenating fields

  any idea?

  Thanks

  Rock

• calvo

  SSChampion

  Points: 12930

  More actions

  May 20, 2010 at 1:11 pm

  #1170410

  declare @sql nvarchar(2000)

  set @sql='select col1,' + ' col2,' + ' col3,' + 'col4 from table'

  exec (@sql)

  try that. don't forget your commas seperating your fields.

  ______________________________________________________________________________________________
  Forum posting etiquette.[/url] Get your answers faster.

• RockingAdmin

  SSCrazy

  Points: 2828

  More actions

  May 20, 2010 at 2:35 pm

  #1170443

  hello calvo

  using the below query will give individual columns.

  declare @sql nvarchar(2000)

  set @sql='select col1,' + ' col2,' + ' col3,' + 'col4 from table'

  exec (@sql)

  actually i want to contatenate all the fields into one field

  ex:

  declare @sql nvarchar(2000)

  set @sql='select (col1' + ' '+' col2' +' '+ ' col3,'+' ' + 'col4) as ttcol from table'

  exec (@sql)

  i think you understood my requirement.

  Thanks

  Rock..

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  May 20, 2010 at 2:59 pm

  #1170453

  You didn't really provide much helpful information about what exactly you're trying to do, but if it's a simple string concatenation with a space between each field then it would be like this:

  declare @sql nvarchar(2000)

  set @sql='select col1 + '' '' + col2 + '' '' + col3 + '' '' + col4 from table'

  exec(@sql)

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• calvo

  SSChampion

  Points: 12930

  More actions

  May 20, 2010 at 4:21 pm

  #1170502

  declare @sql varchar(2000)

  set @sql = 'select firstName' + ' + ' + 'lastName ' + 'from Person.Contact'

  exec (@sql)

  that will concatenate fields into one column. I used SQL Server 2008 and the adventureworks database.

  You can debug our dynamic sql by adding simple print statements to see what you are trying to execute.

  for example;

  declare @sql varchar(2000)

  set @sql = 'select firstName' + ' + ' + 'lastName ' + 'from Person.Contact'

  print @sql

  exec (@sql)

  ______________________________________________________________________________________________
  Forum posting etiquette.[/url] Get your answers faster.

• RockingAdmin

  SSCrazy

  Points: 2828

  More actions

  May 21, 2010 at 6:30 am

  #1170769

  calvo,

  i did little work on this. it works now.Thank you for the support

  Regards,

  Rock..