Bob:
Thank you very much for your input. I can't believe I've gotten to this
point in the process without thinking of simply putting the items I want to
keep into another array.
I read another post regarding the Function you highlighted, but I do require
ORDER BY in my sql.
Thanks again.
Patrick
"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OYneNnS$DHA.2480@TK2MSFTNGP12.phx.gbl...[color=blue]
> Patrick G. wrote (some snippage has occurred):[color=green]
> > Greetings all:
> >
> > ASP VB, SQL Svr 2000
> >
> > I am pulling data from 3 tables.
> >
> > table1 holds item details
> > table2 holds publication types and the item id from table1
> > table3 holds category types and the item id from table1
> >[/color]
>[color=green]
> > Looking for results like so:
> >
> > ItemId ItemTitle ItemDescript ItemCategories
> > ItemPublicationtypes
> > 1 2 This item1 cat1, cat2, cat3
> > pub1, pub2
> >
> > With my view I get 5 rows hence multiple search results when its
> > really the same record.
> >
> > With my ASP I get an array that is populated by using the GetRows
> > method of a recordset.
> >
> > I'm looping through the array to find duplicate records in the data I
> > pulled... when I find a duplicate id I then start another loop to go
> > through and pull out a column value to concatenate with the first
> > duplicate's column value...
> >
> > What I am then left with is a useless item in the array... how do I
> > then remove that "row" and then redim the array after I'm done
> > checking for dups and creating my concatenated strings??
> >[/color]
> Don't bother. read the results into a new array that's been created with[/color]
the[color=blue]
> correct dimensions.
>
> However, you may wish to try something like this:
>
> Create two user-defined functions in SQL Server with these definitions:
>
> Create Function dbo.ConcatCats (@id int)
> Returns varchar(200)
> AS
> DECLARE @str varchar(200)
> Set @str=''
> Select @str = CASE @str WHEN '' THEN ItemCategories
> ELSE @str + ', ' + ItemCategories END
> FROM table3
> WHERE ItemID = @id
> Return @str
>
> Create a similar function for ItemPublicationtypes. Then:
>
> Select ItemID, Itemtitle, ItemDescript,
> dbo.ConcatCats(ItemID), dbo.ConcatPubs(ItemID)
> FROM table1
>
> The technique used in the function has been referred to as "aggregate
> concatenation"; but you should know that MS disavows the use of this
> technique, saying that this behavior is undefined:
>
http://support.microsoft.com/default...;EN-US;q287515
>
> Bottom line: I have used this technique with no ill effects, but do not[/color]
try[color=blue]
> to effect the order in which the values are concatenated to @str. Using an
> ORDER BY clause will yield unpredictable results. If you need the items in[/color]
a[color=blue]
> particular order, go back to your array loop solution.
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>[/color]
