>I am trying to figure out a way to do this in SQL (for a report) but may have to do it in code, but I thought I would ask here first for any insight:
>
>I have a table called meetings, that has basic information about scheduled appointments. Some of the report fields for the report will come straight out of this table. Now there is another table that is called PatList that contains "user defined" elements. This table presents the user with a "Check List" for the scheduled meetings. It has the following fields:
>
>

>PatListID        PrimaryKey
>PatListText      Basically the question that gets asked (Payment Received, Food Required, etc.)
>

>
>The answers supplied by the user to the check-list are stored in a table called PatListAns that has the following fields:
>

>PatListAnsID     PrimaryKey
>PatListID        FK to ChkList Table
>MeetingID        FK to Meetings Table
>RecordID         FK to PATDDOPT Table
>

>
>The available possible answers are user defined also and that table (PATDDOPT) has :
>
>

>RecordID        Primary Key
>StatusText
>

>
>I need to come up with a way to combine the fields from the meetings table, then add a column for each record in the Pat List table, and then get the text answer for those "fields" for each row of the meeting table:
>
>

>meetingID        PatList.[PAYMENT RCVD]   patlist.[Food RQD]
>------------------------------------------------------------------
>123333           PENDING                    NO
>123334           RECEIVED                   YES
>

>
>I'm guessing I'm going to have to create Cursor on the client and then build the fields and then populate that cursor one row at a time with multiple queries....but I'm know I'm not the smartest guy on the planet, so I thought if some one knew how to do it all in a SQL Server call.
>
>Thanks

Excuse this code, I probably don't have all your fields correct but this is the basic idea.

What you're looking at is a 2 step process.
In this solution you'll have to build your sql on the fly because the fields will change over time.

So you'll have to have some code that rotates through you're PatList table and makes a field that looks like this for each row:

"< field Name >  = max(
    case PatListId
              When < PatListId > Then PatDdOpt.StatusText
              Else ""
    End)

I've got some example code below here which shows what the code might look like. The case with a max and group by allows you to do your cross tab query in one sql.

Select 
		MeetingId, 
		pmt_rcvd = max(
			case PatListId
				 When 1 Then PatDdOpt.StatusText
				Else ""
			End),
		food_rqd = max(
			case PatListId
				When 2 Then PatDdOpt.StatusText
				Else ""
			End)
	From PatListAns
	Inner Join PatDdOpt On 
		PatListAns.RecordId = PatDdOpt.RecordId

HTH