SQLTeam.com | Weblogs | Forums

Joining tables using a pivot



I'm trying to create a view by using a Pivot and joining about 3 tables together.
It's for a reservation module where people book appointments. Most data is stored in the main table called:
However custom fields are stored in another table called:
In the first example attached - which works - custom fields are shown in the view ok.

The problem I have now is that people can book appointments for different local offices (Sheffield, Barnsley, Doncaster etc) - these are stored inside another table called:
I'm struggling to reference the fields in this table in my view.

the attached file shows full screenshots of my tables and desired outcome. Any thoughts welcome. thanks for looking.


Please post create table statements for each table and insert statements with sample data


I don't really have any create table statements and insert statements (do I need these?)

I'm just creating a view, like this:

WITH CustomFields  AS (
SELECT ReservationID, [6] AS NI_No, [7] AS Special_Request
	SELECT ReservationID, CustomFieldDefinitionID, Value, Name
	FROM dbo.Reservations_CustomFieldValue, Reservations_Category 
) S
	FOR CustomFieldDefinitionID IN ([6], [7])
	) P 
 SELECT FirstName, NI_No, Special_Request  
    FROM dbo.Reservations_Reservation R
    	JOIN CustomFields C
    		ON R.ReservationID = C.ReservationID;

the above will display the customFields, but I'm not sure where to reference the CategoryID table (as there are 3 select statements).


What Joe was saying... If you'd like our help, you need to provide representative test data so that we're able to test potential solutions. There are quite a few people who are willing to help, but not very many who are willing manually key in data from a screen capture. In short, help us help you.

Have a look here for instructions on how to post these types of questions on a public forum.


thanks for the instructions. Sorry about the poor post.

I have created tables and sample data on SQL Fiddle

Also my desired outcome is something like this: (looks like there are 4 tables I needed to include).

| ReservationName | CategoryName | NI_Number | Requirements |
| Adam --------------- | Amsterdam ----- | AA1 --------- | an apple ------ |
| Brian ---------------- | Barnsley --------- | BB2 -------- | a banana ----- |


this is no longer an issue.

we have decided to go down a different route - using separate calendars for each district/town rather than categories, to simplify the query required.
thanks for looking.