Ask for a Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ask for a Query

Author

Topic

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-10-13 : 18:16:26

Hi,
Please help me to create a query that I'll run in the SQL Query Analyzer to process a table.
The table has 3 fields(A, B, C):
A--- B-------- C
John JohnDoc1 NULL
Matt MattDoc1 NULL
Stan StanDoc1 NULL
Matt MattDoc2 NULL
Matt MattDoc3 NULL
John JohnDoc2 NULL
.......

What I want to do is to write in Field C with the value of:
If the name was repeated in field A then expend the Docname in B and put in field C. If not repeated, then keek the field C Null.
So, after execute the S.P., the table should be like this:
A--- B-------- C
John JohnDoc1 JohnDoc1;JohnDoc2
Matt MattDoc1 MattDoc1;MattDoc2;MattDoc3
Stan StanDoc1 NULL
Matt MattDoc2 MattDoc1;MattDoc2;MattDoc3
Matt MattDoc3 MattDoc1;MattDoc2;MattDoc3
John JohnDoc2 JohnDoc1;JohnDoc2
......

The table has more than 15000 records.
Please help me with any idea.

Thanks.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 18:46:02

declare @c varchar(8000), @a varchar(10)

declare cur cursor FOR
select distinct a from #tab1

open cur
fetch next from cur into @a

while @@fetch_status = 0
begin
set @c = 'update #tab1 set c = replace(ltrim('''
select @c = @c+' '+b from #tab1 where a = @a
select @c = @c + '''),'' '','';'') from #tab1 where a = '''+@a+''''

execute(@c)

fetch next from cur into @a
end

close cur
deallocate cur

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 01:31:49

see here also

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-10-14 : 11:38:02

Thank you very much for the code and the link.
I get the table updated with the result I wanted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources