making an array out of several columns

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)

making an array out of several columns

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-26 : 12:02:46

My table looks like :
create table well_comments(
uwi varchar(13),
comment_type varchar(25),
comment_seq int,
comment varchar(72)
)

The Primary key on the table will be one UWI, Comment_type, comment_seq

I wish to do something simular as
http://www.sqlteam.com/item.asp?ItemID=11021

This article looks like it works great, however I lack a sequential column such as I to work with. UWI is varchar as it contains chars and numbers combined.

I guess I could go through and assign a I column to the table and populate it based on the keys.

I've made a cursor based method of doing this... this iterative version has 2 while loops and takes waaaaay to long in doing so. Any ideas with how to make this set based?

-----------------------
SQL isn't just a hobby, It's an addiction

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-26 : 13:39:31

Take another look at the article. You don't need a sequential int column to use my set based method....

Jay White
{0}

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-26 : 14:32:48

Hmmm, I still think it looks like it does. Here's what I did... and it works:

--create a table to work with
create table #workA (
i int identity(1,1) not null,
UWI varchar(13) not null,
type varchar(25)
)
insert into #workA (uwi,type)
select distinct uwi, resource_comment_type
from well_resource_comments_DTS

create table #workB (
i int not null,
UWI varchar(13) not null,
type varchar(25) not null,
comment varchar(75),
list varchar(7904)
)

Insert into #workB(i,uwi,type,comment)
select a.I,A.UWI,A.type,WRC.resource_comments
from #workA A inner join well_resource_comments_DTS WRC on a.uwi = WRC.uwi and convert(varchar(25),A.type) = convert(varchar(25),WRC.resource_comment_type)
order by i, comment_seq
--here is the meat of the work
declare
@list varchar(8000),
@lasti int

select
@list = '',
@lasti = -1

--here is the meat of the work
update
#workB
set
@list = list = case
when @lasti <> i then comment
else rtrim(@list) + ' ' + comment
end,
@lasti = i
select top 50 * from #workb

-----------------------
SQL isn't just a hobby, It's an addiction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources