Lookup a table sideways with row names. Is there another way?

recordset object is to slow.  Even if I just loop thru an array it adds miliseconds to execution, I timed it and found that it's faster to just query the database than parse/filter/order an array.  Even the sp I wrote is really fast, just big and ulgy, and I don't know everything about SQL so I thought someone greater than me might know how to do this in SQL.  The only adodb methods I'm going to use in the app is execute, getrows and getstring, unless someone can show me a faster straight asp solution.  The thought did occur to me to use the recordset object, but it's just too slow.

-Coolhand2120

a ~slightly~ smaller way below:

select 'ContactID', contactID from contacts where contactID = @contactID
union all
select 'FirstName', FirstName from contacts where contactID = @contactID
union all
select 'LastName', LastName from contacts where contactID = @contactID
union all


etc...

Are the actual list of field names fixed?

Ya the field names never change, but they are the column names of the table I'm querying.  I was really looking for another way to do it, I think there is a better way to do this besides 20+select statments.  But that does trim it up quite a bit.

-Coolhand2120

You can do a 'cross join' which will turn one contact record into many.

For example

Create a table called tblFieldList with one varchar column called FieldName

Populate it with your list of field names.


Then run this statement:


select
L.FieldName,
C.contactID,
C.FirstName,
C.LastName,
....
from
contacts C
CROSS JOIN
tblFieldList L
where contactID = @contactID)


You'll get all the records you need, but every record has ALL the columns in it. (rather than just one). Anyway run that and see if ti provides any inspiration.

You could then wrap that statement in something else to pluck out the individual field you need.


But you still come back to having to explicitly define column names in your select statement.

That is a novel approach but I think it's more code than the original, it's for sure more records/columns.  Let me work with your cross join idea and see if I can make somthing work.

-Coolhand2120

This is the best I can come up with using cross join, it looks up the table columns from syscolumns but the probelm is that I can't dictate what column shows up as column 1, but it's fast and small, so I'm half way there.   I'm stumped, it's just mind boggling.

-Coolhand2120

select * from syscolumns
cross join contacts
where id = (select id from sysobjects where name = 'contacts')
and contactid = 'BCD549F0-0787-4AF2-82CD-85C4AE6D2872'