Table with Fields as records 1

[MikeAuz1979]

Hi,

Using A2K I'm trying to pull data from a sql server table that is formatted as below:

RFSID RFS_FIELD_CODE RFS_FIELD_VALUE
1 Street Number 26
1 Street Name Jobbers Lane
1 Suburb MinchinBury
2 Street Number 18
2 Street Name Poop St
2 Suburb Shrewsbury

So basically the fields are stored as records (I guess so you can add new fields without having to change the table)

Does anyone have any idea how I can show this data as:

RFSID Street Number Street Name Suburb
1 26 Jobbers Lane MinchinBury
2 18 Poop St Shrewsbury

Thanks for any help
Mike

 

[PHV]

Something like this ?
SELECT A.RFSID
, A.RFS_FIELD_VALUE AS [Street Number]
, B.RFS_FIELD_VALUE AS [Street Name]
, C.RFS_FIELD_VALUE AS Suburb
FROM (yourTable AS A
INNER JOIN yourTable AS B ON A.RFSID = B.RFSID)
INNER JOIN yourTable AS C ON A.RFSID = C.RFSID
WHERE A.RFS_FIELD_CODE = 'Street Number'
AND B.RFS_FIELD_CODE = 'Street Name'
AND C.RFS_FIELD_CODE = 'Suburb'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MikeAuz1979]

Works perfectly! thanks for your help!