Click here to Skip to main content
15,890,946 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
See more:
hi everybody I have a problem with sql query, I have a table having data like..

GrpId   Item
1       Mango
1       Apple
2       Chair
2       Table
3       Computer
3        T.V.
4        Scooter
4        Car


i want to get result by GrpId I have 6 row in my table actualy but if i think by GrpId then there in one group two items so I want output like that

1  Mango/Apple
2  Chair/Table
3  Computer/T.V
4  Scooter/Car

plz help me on this topic

Thanks in advance
Posted
Comments
Corporal Agarn 20-May-11 8:59am    
What have you tried so far?

1 solution

try to find out in the following way..
VB
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

//Result 
inident1    items1
----------- -------------------
1           Mango/Apple/Chair
2           table/Computer
3           TV
4           Scooty/Car
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900