Joining tables using a pivot

Transact-SQL
,
1

Reservations_screenshot
Reservations_screenshot.gif1528×1333 75.2 KB

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:
'Reservations_Reservation'.
However custom fields are stored in another table called:
'Reservations_CustomFieldValue'
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:
'Reservations_Category'
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.

2

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

2 Likes
3

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
FROM
(
	SELECT ReservationID, CustomFieldDefinitionID, Value, Name
	FROM dbo.Reservations_CustomFieldValue, Reservations_Category 
) S
PIVOT
(
	MAX(Value)
	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).

1 Like
4

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.

2 Likes
5

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 ----- |
+-----------------+--------------+-----------+--------------+

1 Like
6

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.