-
Select Question
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~~!
-
Take look at 'cross tab report' in sql books online, may give you some idea.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|