Remove items from GetRows array

Discussion in 'ASP General' started by Patrick G., Feb 27, 2004.

  1. Patrick G.

    Patrick G. Guest

    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
    Patrick G., Feb 27, 2004
    #1
    1. Advertising

  2. Patrick G.

    Bob Barrows Guest

    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"
    Bob Barrows, Feb 27, 2004
    #2
    1. Advertising

  3. Patrick G.

    Patrick G. Guest

    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" <> wrote in message
    news:OYneNnS$...
    > 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"
    >
    >
    Patrick G., Feb 27, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Croney69

    Using GetRows()

    Croney69, Jul 28, 2003, in forum: ASP General
    Replies:
    5
    Views:
    160
    Bob Barrows
    Jul 28, 2003
  2. Moshe

    GetRows Mystery

    Moshe, Aug 28, 2003, in forum: ASP General
    Replies:
    8
    Views:
    122
    Bob Barrows
    Aug 28, 2003
  3. Crane Linkledder

    Difference between getrows and getstring

    Crane Linkledder, Oct 29, 2003, in forum: ASP General
    Replies:
    11
    Views:
    252
    Aaron Bertrand - MVP
    Oct 30, 2003
  4. Laphan
    Replies:
    11
    Views:
    309
    StephenMcC
    Nov 22, 2004
  5. Laphan
    Replies:
    0
    Views:
    109
    Laphan
    Nov 29, 2005
Loading...

Share This Page