Appending nvarchar in a variable

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  January 21, 2009 at 12:39 pm

  #128160

  Hi folks, trying to do something new here:

  I'm trying to append the selected data of a select in a string, without using a cursor:

  Here's what I've come with so far:

  use tempDB

  GO

  if exists (select 1 from information_Schema.tables where table_name = 'TEST')

  drop table test

  create table TesT

  (ID int identity(1,1),

  MfrCode nvarchar(5))

  insert into Test

  select 'SCH'

  union ALL

  select 'SQD'

  union ALL

  select 'LEV'

  union ALL

  select 'SCH'

  union ALL

  select 'SCH'

  union ALL

  select 'LEV'

  union ALL

  select 'POV'

  union ALL

  select 'MANUI'

  union ALL

  select 'TB'

  union ALL

  select 'WIRE'

  select * from Test

  declare @TestAppend nvarchar(500)

  select @TestAppend = ''

  select @testAppend = @TestAppend + ', ' + MfrCode from test

  select @TestAppend

  This appends the "MfrCode" in the string with a comma between them, which is nice. What I would like to do is select only the distinct values, but when I add the distinct keyword to the query, it fails. Anyone has another idea?

  Thanks in advance,

  Cheers,

  J-F

  Cheers,

  J-F

• jminette

  Ten Centuries

  Points: 1352

  More actions

  January 21, 2009 at 12:55 pm

  #930644

  SELECT @TestAppend = @TestAppend + ', ' + a.MfrCode from (select distinct MfrCode from test) a

  Does that give you what you're looking for?

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  January 21, 2009 at 1:02 pm

  #930653

  Wow.. I can't believe I didn't see that. I was trying to fit the distinct keyword somewhere around the code, without doing a subquery. Now that I see it, it looks so obvious.

  Thanks a lot,

  Cheers,

  J-F

  Cheers,

  J-F