Select Question

[a5718800]

I got a table with 2 columns.

A B
-------------
aa aa
aa bb
bb aa
aa aa
bb bb

and how can i select out the result like below

A B
------------
aa aa,aa,bb
bb aa,bb


it looks like need to use cursor.
but it is late. my brain is not moving...(maybe i am suck)
thanks~~!

[rmiao]

Take look at 'cross tab report' in sql books online, may give you some idea.

[NitinKhurana]

the solution im givin may not be the best 1, but works ,
u can modify it for ur problem....


CREATE TABLE test_name1
(
[name1] [varchar](50)
[val1] [int] NULL
)

insert into test_name1 values('nitin',2)
insert into test_name1 values('nitin',1)
insert into test_name1 values('another',1)
insert into test_name1 values('another',10)

alter procedure simple_proc
AS
BEGIN
DECLARE @counter int
DECLARE @var varchar(50)
DECLARE @var2 varchar(50)
DECLARE @name varchar(50)

create table #temp
( counter int identity (1,1),
name1 varchar(20),
val1 varchar(50) )

create table #temp1
(name1 varchar(20),
val1 varchar(50))

insert into #temp (name1,val1) select name1,val1 from test_name1

set @counter =1
while @counter <= (select max(counter) from #temp)
begin
set @var= (select top 1 val1 from #temp )
set @name=(select top 1 name1 from #temp)
delete from #temp where counter = @counter
if((select count(name1) from #temp1 where name1 like @name)>0)

begin
set @var2 = (select val1 from #temp1 where name1 like @name)
update #temp1 set val1 = (@var2+','+@var) where name1 like @name
set @counter =@counter+1
end
else
begin
insert into #temp1 values(@name,@var)
set @counter =@counter+1
end
end
select * from #temp1
END


--exec simple_proc