try to find out in the following way..
grpid Item
----------- ------------------
1 Mango
1 Apple
1 Chair
2 table
2 Computer
3 TV
4 Scooty
4 Car
//tried this
declare @Inloop int
set @Inloop=1
declare @grpid int
declare @RItems varchar(50)
declare @Counter int
declare @table2 table(inident1 int identity(1,1),items1 varchar(50))
declare @table table(ident int identity(1,1),Number int,grpId int)
insert into @table select Count(grpid),grpId from test8 group by grpid
select @OutCounter=Count(*) from @table
while (@Outloop <=@OutCounter)
begin
create table #table1(inident int identity(1,1),items varchar(50))
select @grpid= grpId from @table where grpId= @Outloop
print @Outloop
insert into #table1 select Item from test8 where grpId = @grpid
select @InCounter=Count(*) from #table1
print @InCounter
print @Inloop
while(@Inloop<=@InCounter)
begin
select @RItems=items from #table1 where inident=@Inloop
if(@Inloop=1)
begin
insert into @table2 values(@RItems)
end
else
begin
Update @table2 set items1=items1+'/'+@RItems where inident1=@Outloop
end
set @Inloop=@Inloop+1
end
drop table #table1
set @InCounter=0
set @Inloop=1
set @Outloop=@Outloop+1
end
select * from @table2
inident1 items1
----------- -------------------
1 Mango/Apple/Chair
2 table/Computer
3 TV
4 Scooty/Car