December 28, 2007 at 11:33 am
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
December 28, 2007 at 12:10 pm
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
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply