Array into another Array

W

webb

I have a table with a huge amount of data.
At present my data is like so.


userid:1588 | 1 | CR1
userid:1588 | 2 | CR2
userid:1588 | 1 | CR3
userid:1588 | 1 | CR4
userid:1588 | 1 | CR5
userid:1588 | 2 | CR6


Intened format below:


userid | CR1| CR2| CR3| CR4| CR5| CR6 "Columns"
1588| 1| 2| 1| 1| 1| 2 "Data"


I already figured out how to get the data out with some great help.
But it works for one record.

I have 4342 records how can i loop the routine below to get all id's fro me.

I tried the following but i'm missing something.



dim ID, fArray
str ="SELECT userid FROM RESULTED GROUP BY userid"
Set rsx = cn80.Execute(str)

Do while not rsx.EOF
ID = rsx("userid")
fArray = fArray & ID &","
rsx.movenext
Loop

members = fArray
selections = Split(members, ",")
for j = 0 To UBound(selections)

'run the normail routine here which is below

next




<%
dim userid, numbers, cid, sArray

str="SELECT userid, critic_result, criticId from RESULTED WHERE userid='" &
selections(j)& "'"
Set rs = cn80.Execute(str)

Do while not rs.EOF

numbers = rs("critic_result")
cid = rs("criticId")
userid = rs("userid")
sArray = sArray & "cr"&cid &"="& numbers &","
rs.movenext
Loop

'Response.write sArray

' open recordset
str_sql2 = "INSERT INTO SurveyResultsExported (userid, "
Dim strNames,strValues2

tempArr = Split(sArray,",")
for i = 0 to (UBound(tempArr)-1)
Values = split(tempArr(i),"=")

'CREATE FIELD NAME LIST
strNames = strNames & Values(0) & ","

'CREATE VALUE LIST
strValues2 = strValues2 & "'" & Values(1) & "', "

next

'BUILD SQL STRING then go for it

str_sql2 = str_sql2 & left(strNames, (Len(strNames)-1)) & ")
VALUES ("&pracid&", " & left(strValues2, (Len(strValues2)-1)) & ")"


'cn80.execute(str_sql2)
response.write(str_sql2)
response.end
cn80.Close
Set cn80 = Nothing
%>


Thank you for you help.

webb
 
E

Egbert Nierop \(MVP for IIS\)

webb said:
I have a table with a huge amount of data.
At present my data is like so.


userid:1588 | 1 | CR1
userid:1588 | 2 | CR2
userid:1588 | 1 | CR3
userid:1588 | 1 | CR4
userid:1588 | 1 | CR5
userid:1588 | 2 | CR6
Click to expand...
Intened format below:


userid | CR1| CR2| CR3| CR4| CR5| CR6 "Columns"
1588| 1| 2| 1| 1| 1| 2 "Data"


I already figured out how to get the data out with some great help.
But it works for one record.

I have 4342 records how can i loop the routine below to get all id's fro
me.

I tried the following but i'm missing something>
Click to expand...


There is a very convenient function GetSTring on the Recordset object
Sample from MSDN

This example demonstrates the GetString method.

Assume you are debugging a data access problem and want a quick, simple way
of printing the current contents of a small Recordset.

'BeginGetStringVB

'To integrate this code
'replace the data source and initial catalog values
'in the connection string

Public Sub Main()
On Error GoTo ErrorHandler

' connection variables
Dim Cnxn As ADODB.Connection
Dim rstAuthors As ADODB.Recordset
Dim strCnxn As String
Dim strSQLAuthors As String
Dim varOutput As Variant

' specific variables
Dim strPrompt As String
Dim strState As String

' open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Cnxn.Open strCnxn

' get user input
strPrompt = "Enter a state (CA, IN, KS, MD, MI, OR, TN, UT): "
strState = Trim(InputBox(strPrompt, "GetString Example"))

' open recordset
Set rstAuthors = New ADODB.Recordset
strSQLAuthors = "SELECT au_fname, au_lname, address, city FROM Authors "
& _
"WHERE state = '" & strState & "'"
rstAuthors.Open strSQLAuthors, Cnxn, adOpenStatic, adLockReadOnly,
adCmdText

If Not rstAuthors.EOF Then
' Use all defaults: get all rows, TAB as column delimiter,
' CARRIAGE RETURN as row delimiter, EMPTY-string as null delimiter
varOutput = rstAuthors.GetString(adClipString)
' print output
Debug.Print "State = '" & strState & "'"
Debug.Print "Name Address City" & vbCr
Debug.Print varOutput
Else
Debug.Print "No rows found for state = '" & strState & "'" & vbCr
End If

' clean up
rstAuthors.Close
Cnxn.Close
Set rstAuthors = Nothing
Set Cnxn = Nothing
Exit Sub

ErrorHandler:
' clean up
If Not rstAuthors Is Nothing Then
If rstAuthors.State = adStateOpen Then rstAuthors.Close
End If
Set rstAuthors = Nothing

If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndGetStringVB
 
B

Bob Barrows [MVP]

webb said:
I have a table with a huge amount of data.
At present my data is like so.


userid:1588 | 1 | CR1
userid:1588 | 2 | CR2
userid:1588 | 1 | CR3
userid:1588 | 1 | CR4
userid:1588 | 1 | CR5
userid:1588 | 2 | CR6


Intened format below:


userid | CR1| CR2| CR3| CR4| CR5| CR6 "Columns"
1588| 1| 2| 1| 1| 1| 2 "Data"


I already figured out how to get the data out with some great help.
But it works for one record.

I have 4342 records how can i loop the routine below to get all id's
fro me.
Click to expand...
What database type and version are you using?
 
B

Bob Barrows [MVP]

OK, you can use a series of CASE statements to accomplish your
pivot/crosstab. This is untested, but it should get you started:

select userid,
MAX(CASE criticId WHEN 'CR1' THEN critic_result END) CR1,
MAX(CASE criticId WHEN 'CR2' THEN critic_result END) CR2,
MAX(CASE criticId WHEN 'CR3' THEN critic_result END) CR3,
MAX(CASE criticId WHEN 'CR4' THEN critic_result END) CR4,
MAX(CASE criticId WHEN 'CR5' THEN critic_result END) CR5,
MAX(CASE criticId WHEN 'CR6' THEN critic_result END) CR6
FROM RESULTED
GROUP BY userid
 
W

webb

I'm a bit lost here with what you've written.

I'm trying to accomplish my routine to run without me supplying a static
userid.

webb
 
B

Bob Barrows [MVP]

Did you try running this sql statement in Query Analyzer? You should see
that it will give you the results you want.

Bob Barrows
 
W

webb

The column name CR exists in the table SurveyResultsExported.


This what I'm trying to do but i get an error i can't see...


Code: 
'Pick all unique userids
dim ID, fArray
str ="SELECT DISTINCT userid FROM RESULTED ORDER BY userid"
Set rsx = cn80.Execute(str)

Do while not rsx.EOF
ID = rsx("userid")
fArray = "," & fArray & ID
rsx.movenext
Loop

members = Mid(fArray,2)
selections = Split(members, ",")
for j = 0 To UBound(selections)

dim userid, numbers, cid, sArray

'	Select all critic results for the intended user
str="SELECT userid, critic_result, criticId from RESULTED WHERE userid='" &
selections(i) & "'"
Set rs = cn80.Execute(str)
Do while not rs.EOF

numbers = rs("critic_result")
cid = rs("criticId")
pracid = rs("userid")
sArray = &"," & sArray & "cr"&cid &"="& numbers
rs.movenext
Loop

sArray = Mid(sArray,2)

'	Collate fields and values
str_sql2 = "INSERT INTO SurveyResultsExported (userid, "
Dim strNames,strValues2

tempArr = Split(sArray,",")

for i = 0 to Ubound(tempArr)
Values = split(tempArr(i),"=")

'CREATE FIELD NAME LIST
strNames = strNames & Values(0) & ","

'CREATE VALUE LIST

strValues2 = strValues2 & "'" & Values(1) & "', "

next

'BUILD SQL STRING then go for insertion

str_sql2 = str_sql2 & left(strNames, (Len(strNames)-2)) & ") VALUES
("&pracid&", " & left(strValues2, (Len(strValues2)-2)) & ")"
'cn80.execute(str_sql2)
response.write(str_sql2)
response.end
cn80.Close
Set cn80 = Nothing

next
 
B

Bob Barrows [MVP]

Wait a minute. Here is what you said your "intened format" was:

userid | CR1| CR2| CR3| CR4| CR5| CR6 "Columns"
1588| 1| 2| 1| 1| 1| 2 "Data"

I do not see a column named "CR" in the intended results.


Forget your loop. You can accomplish the crosstab using a sql statement,
given that what you stated in the befinning was correct. You do not have to
insert the data into another table.

Just open Query Analyzer, paste in the sql I gave you and try it. You will
see.

Bob Barrows
The column name CR exists in the table SurveyResultsExported.


This what I'm trying to do but i get an error i can't see...


Code: 
'Pick all unique userids
dim ID, fArray
str ="SELECT DISTINCT userid FROM RESULTED ORDER BY userid"
Set rsx = cn80.Execute(str)

Do while not rsx.EOF
ID = rsx("userid")
fArray = "," & fArray & ID
rsx.movenext
Loop

members = Mid(fArray,2)
selections = Split(members, ",")
for j = 0 To UBound(selections)

dim userid, numbers, cid, sArray

' Select all critic results for the intended user
str="SELECT userid, critic_result, criticId from RESULTED WHERE
userid='" & selections(i) & "'"
Set rs = cn80.Execute(str)
Do while not rs.EOF

numbers = rs("critic_result")
cid = rs("criticId")
pracid = rs("userid")
sArray = &"," & sArray & "cr"&cid &"="& numbers
rs.movenext
Loop

sArray = Mid(sArray,2)

' Collate fields and values
str_sql2 = "INSERT INTO SurveyResultsExported (userid, "
Dim strNames,strValues2

tempArr = Split(sArray,",")

for i = 0 to Ubound(tempArr)
Values = split(tempArr(i),"=")

'CREATE FIELD NAME LIST
strNames = strNames & Values(0) & ","

'CREATE VALUE LIST

strValues2 = strValues2 & "'" & Values(1) & "', "

next

'BUILD SQL STRING then go for insertion

str_sql2 = str_sql2 & left(strNames, (Len(strNames)-2)) & ") VALUES
("&pracid&", " & left(strValues2, (Len(strValues2)-2)) & ")"
'cn80.execute(str_sql2)
response.write(str_sql2)
response.end
cn80.Close
Set cn80 = Nothing

next
Click to expand...
 
W

webb

Yes!!!!!!!!!!
I finally can see very sweet approach.....How could i just insert the data
into another database.

Much appreciated

Thank you

webb
 
R

Roland Hall

: Yes!!!!!!!!!!
: I finally can see very sweet approach.....How could i just insert the data
: into another database.

I hope you don't expect us to refer to you as Sweet Bob now...
 
B

Bob Barrows [MVP]

Is the other database on the same server? If so, just use an INSERT...SELECT
statement:

INSERT INTO db2..table (<list of columns>)
SELECT ...


If the other database is on a different server, I would use DTS to export
the results of the query. You can find info about DTS at www.sqldts.com.

Bob Barrows
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Output Parameter? 1
split array twice and do a multiple insert 1
Something I am doing with Session seems funky 1
Python point location of intersect between two lines 0
Object reference error - Microsoft code 2
Problem filtering recordset by values held in array 8
Need some help with login script 4
carrying over an array from sql into another sub-routine 1

Members online

No members online now.
Total: 35 (members: 0, guests: 35)
Robots: 424

Forum statistics

Threads
473,768
Messages
2,569,574
Members
45,051
Latest member
CarleyMcCr

Latest Threads

Top