concatenation problem

• GSquared

  SSC Guru

  Points: 260824

  More actions

  December 28, 2007 at 11:33 am

  #763361

  set nocount on

  declare @customers table (

  Row int identity primary key,

  ID int NOT NULL,

  Number [smallint] NULL ,

  starting_point [varchar] (5) NULL ,

  ending_point [varchar] (5) NULL

  )

  insert into @customers values (888,1, 'abc', 'def')

  insert into @customers values (888,2, 'def', 'abc')

  insert into @customers values (777,1, 'pqr', 'stu')

  insert into @customers values (777,2, 'stu', 'pqr')

  insert into @customers values (666,1, 'abc', 'def')

  insert into @customers values (666,2, 'def', 'pqr')

  insert into @customers values (666,3, 'pqr', 'abc')

  --select * from @customers

  declare @T1 table (

  SetID int,

  String varchar(100))

  declare @Rows int, @Row int, @STR varchar(100), @StrID int,

  @Rows2 int, @Row2 int, @MaxRow int

  select @rows = count(*), @row = 1

  from @customers

  while @row <= @rows

  begin

  select @strid = id

  from @customers

  where row = @row

  select @rows2 = count(*), @row2 = 1, @STR = '', @maxrow = max(row)

  from @customers

  where id = @strid

  while @row2 <= @rows2

  begin

  select @STR = @STR + starting_point +

  case

  when @row2 < @rows2 then '/'

  else '/' + ending_point

  end

  from @customers

  where id = @strid

  and number = @row2

  --raiserror(@str, 10, 1) with nowait

  select @row2 = @row2 + 1

  end

  insert into @t1 (setid, string)

  select @strid, @STR

  select @row = min(row)

  from @customers

  where row > @maxrow

  end

  select * from @t1

  set nocount off

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  December 28, 2007 at 12:10 pm

  #763376

  Heh... a While loop... "Firehose" cursor would be just as good... take a look at the function I made in this thread... no explicit loop... no cursor... still basically hidden RBAR because concatenation of this nature in SQL Server isn't something that should really be done except when building something like Dynamic SQL.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)