Transact SQL :: Limiting Recordset Output

I’m thinking about the best way to run these queries, which need to be run regularly.

The first query is two tables linked, one is a data table containing a unique person identifier and three activity fields, the second a lookup table with an activity_type linked to the activity in the table data.

Data Table
PersonID
Lots of other fields
Activity1
Activity2
Activity3

The ACTIVITY fields can contain data anywhere between all being NULL and all being complete.

Lookup Table
ActivityID
ActivityDesc ( which appears in Activity 1 – 3)
ActivityType

I’d like to create a function which will create a recordset containing the Person ID and the Activity Type. I am unsure as to whether to do this in a way which will create one record for each person, or potentially 3 records for each person. This is how I have done the 3 records:

SELECT PersonID, Activity1 As Sport, ActivityType
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity1 = dbo.tblLUActivityType.ActivityDesc
UNION
SELECT PersonID, Activity2 As Sport, ActivityType

[Code] ...

And this is how I have done the 1 record:

SELECT ClientID,
Activity1,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity1) As ActivityType1,
Activity2,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity2) As ActivityType2,
Activity3,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity3) As ActivityType3
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity3 = dbo.tblLUActivityType.ActivityDesc
Order by PersonID

The reason I would like to do this is because I need to create a stored procedure which returns one record per person with two fields (Person Id, ActivityType) which states their ActivityType depending on certain rules:

Rule 1: If any of Activity 1 – 3 are ‘O’ report the Person ID and ActivityType = ‘O’

Rule 2:  Of the rest of the recordset, if any of Activity 1 – 3 are ‘N’ but none of Activity 1-3 are ‘O’ or ‘A’ then report the Person ID and ‘N’.

Rule 3:  Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ but none of Activity 1-3 are ‘O’ or ‘N’ then report the Person ID and ‘A’.

Rule 4: Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ and one of the is ‘N’ then report the Person ID and ‘AN’.

At the end this I’m looking for one recordset with two fields containing a personID and one of ‘O’, ‘A’, ‘N’ or ‘AN’. I can do the first part of this in any way necessary, as long as the second part returns the recordset I need.