Remove items from GetRows array

[Patrick G.]

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

when I create a view on these tables joined together I end up getting
multiple rows for table1 items when they belong to more than 1 publication
or category.
What I am trying to do is find those "duplicate" rows and create a string or
the publication types and category types to essentially create 1 row... I
researched doing this with some T-SQL but no method seemed to be accurate
and quick on performance.

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??

here's the code:

For iCounter = 0 To iLibResultsCount
If LibCountDups(arrLibResults(0, iCounter)) = True Then '** Use a
function to get the count of like ID values
For iSubCounter = 0 To iLibResultsCount
If arrLibResults(0, iCounter) = arrLibResults(0, iSubCounter) Then
If arrLibResults(9, iCounter) <> arrLibResults(9, iSubCounter) Then
strCatList = strCatList + ", " & arrLibResults(9, iSubCounter)
arrLibResults(0, iSubCounter) = ""
arrLibResults(1, iSubCounter) = ""
arrLibResults(2, iSubCounter) = ""
arrLibResults(3, iSubCounter) = ""
arrLibResults(4, iSubCounter) = ""
arrLibResults(5, iSubCounter) = ""
arrLibResults(6, iSubCounter) = ""
arrLibResults(7, iSubCounter) = ""
arrLibResults(8, iSubCounter) = ""
arrLibResults(9, iSubCounter) = ""
arrLibResults(10, iSubCounter) = ""
arrLibResults(11, iSubCounter) = ""
End If

If arrLibResults(11, iCounter) <> arrLibResults(11, iSubCounter) Then
strPubList = strPubList + ", " & arrLibResults(11, iSubCounter)
arrLibResults(0, iSubCounter) = ""
arrLibResults(1, iSubCounter) = ""
arrLibResults(2, iSubCounter) = ""
arrLibResults(3, iSubCounter) = ""
arrLibResults(4, iSubCounter) = ""
arrLibResults(5, iSubCounter) = ""
arrLibResults(6, iSubCounter) = ""
arrLibResults(7, iSubCounter) = ""
arrLibResults(8, iSubCounter) = ""
arrLibResults(9, iSubCounter) = ""
arrLibResults(10, iSubCounter) = ""
arrLibResults(11, iSubCounter) = ""
End If
End If
Next
arrLibResults(9, iCounter) = arrLibResults(9, iCounter) & strCatList
arrLibResults(11, iCounter) = arrLibResults(11, iCounter) & strPubList
End If
Next

 

[Bob Barrows]

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

 

[Patrick G.]

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