Re: Remove items from GetRows array

From: Bob Barrows (reb01501_at_NOyahoo.SPAMcom)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 07:04:08 -0500

Patrick G. wrote (some snippage has occurred):
> 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
>

> 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??
>
Don't bother. read the results into a new array that's been created with the
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.aspx?scid=kb;EN-US;q287515

Bottom line: I have used this technique with no ill effects, but do not try
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 a
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"

Relevant Pages

  • Remove items from GetRows array
    ... I am pulling data from 3 tables. ... table2 holds publication types and the item id from table1 ... the publication types and category types to essentially create 1 row... ... I'm looping through the array to find duplicate records in the data I ...
    (microsoft.public.inetserver.asp.general)
  • Re: Functions, Arrays and number/text stored as text
    ... May I request you to mail me the workbook at ask@xxxxxxxxxxxxxxxx and explain the problem very clealry. ... Table1 is basically a database holding hundreds of lines. ... everything in Column-A was numbers stored as numbers. ... number (IF/MAX array), etc. ...
    (microsoft.public.excel.worksheet.functions)
  • Functions, Arrays and number/text stored as text
    ... I have a small array problem. ... Let's call them Table1 and Table2. ... Table1 is basically a database holding hundreds of lines. ... To do this I used mostly VLOOKUPS and ARRAYS. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Functions, Arrays and number/text stored as text
    ... Let's call them Table1 and Table2. ... Table1 is basically a database holding hundreds of lines. ... To do this I used mostly VLOOKUPS and ARRAYS. ... number (IF/MAX array), etc. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Remove items from GetRows array
    ... >> With my ASP I get an array that is populated by using the GetRows ... >> through and pull out a column value to concatenate with the first ... > The technique used in the function has been referred to as "aggregate ... I have used this technique with no ill effects, ...
    (microsoft.public.inetserver.asp.general)
Quantcast