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 2 2440
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...;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:
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" <re******@NOyahoo.SPAMcom> wrote in message
news:OY**************@TK2MSFTNGP12.phx.gbl... 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...;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"
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Croney69 |
last post by:
I am getting information out of a table to place into to
an Array.
rs=DataConn.Execute(strSQL)
At this point I place it into the array
objArray=rs.GetRows()
But how do I handle things if...
|
by: Moshe |
last post by:
I'm facing an odd behavior in using the GetRows Method. I'm not sure what's
causing it because it has been working fine until now.
I have a sproc that returns 1 row. I display the RS info on the...
|
by: Crane Linkledder |
last post by:
What is the difference, and when to use one over the other?
Thanks
|
by: Sam Collett |
last post by:
How do I remove an item with a specified value from an array?
i.e. array values 1,2,2,5,7,12,15,21
remove 2 from array would return
1,5,7,12,15,21
(12 and 21 are NOT removed, duplicates are...
|
by: Laphan |
last post by:
Hi All
I'm using .getRows() with a local var array instead of doing a recursive
loop so that I'm being a good ASP newvbie and closing my object i/o's (the
recordset in this case) as quick as...
|
by: bajopalabra |
last post by:
hi
session("myVar") = rs.getRows( )
don't work when number of records
is greater than 10
does anybody know WHY ???
is it a Session object limitation ???
thanks
|
by: Hank |
last post by:
Hello,
I'm having trouble reading from a table directly into an
array using Access 2000.
Dim db As Database
Dim rsTime As Recordset
Dim TimeArray As Variant
Set db = CurrentDb
Set rsTime =...
|
by: ADezii |
last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows()...
|
by: ADezii |
last post by:
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |