View Single Post
  #1 (permalink)  
Old July 2nd, 2007, 04:35 AM
rodmcleay rodmcleay is offline
Friend of Wrox
Points: 2,376, Level: 20
Points: 2,376, Level: 20 Points: 2,376, Level: 20 Points: 2,376, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , Australia.
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default Results in line from sam table

Hi All,
I wish to build a query that will return a resultset compiling records from the same table.

The table is something like this
tblTestData
ID GroupID FieldID FieldValue
1 1 1 'test11'
1 1 2 'test12'
1 1 3 'test13'
1 2 1 'test21'
1 2 2 'test22'
1 2 3 'test23'
1 3 1 'test31'
1 3 2 'test32'
1 3 3 'test33'

I wish to be able to create a SP to return the fields in one record for each "Group"

So the result set would be
ID Group Field1 Field2 Field3
1 1 'test11' 'test12' 'test13'
1 2 'test21' 'test22' 'test23'
1 3 'test31' 'test32' 'test33'

I had hoped to provide the paramters ID,Field1ID, Field2ID, Field3ID to obtain this result set but can not get the relationship correct without doing what seems to be a huge volume of sub queries.

Can anyone point me in the right direction as to what type of constuct/pattern I should be using for this type of query.


Many thanks in advance for any assistance.




======================================
"They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad."
--Shakespeare
======================================
__________________
======================================
"They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad."
--Shakespeare
======================================
Reply With Quote