CREATE TABLE TEMP1	-- Parent Table
(
	T1_ID int,
	T1_NonDate1 int,
	T1_NonDate2 varchar(10)
	PRIMARY KEY
	(
		T1_ID
	)
)

CREATE TABLE TEMP2	-- Child Table of Event Types and Dates
(
	T2_ID int,
	T2_T1_ID int,
	T2_Type varchar(4),
	T2_Date datetime,
	PRIMARY KEY
	(
		T2_ID
	)
)

INSERT INTO TEMP1
SELECT 1, 1234, 'FOO' UNION ALL
SELECT 2, 5678, 'BAR'

INSERT INTO TEMP2
SELECT 1001, 1, 'AAA', '20000101' UNION ALL -- AAA is distinct
SELECT 1002, 1, 'BBB', '20000102' UNION ALL -- BBB is distinct
SELECT 1003, 1, 'CCC', '20000201' UNION ALL -- CCC can have multiple rows for each Parent record
SELECT 1004, 1, 'CCC', '20000202' UNION ALL
SELECT 1005, 1, 'CCC', '20000203' UNION ALL
--
SELECT 1006, 2, 'AAA', '20010101' UNION ALL
SELECT 1008, 2, 'CCC', '20010201' UNION ALL
SELECT 1009, 2, 'CCC', '20010202'
GO

-- Sample Query
SELECT	T1_ID, T1_NonDate1, T1_NonDate2, [AAA]=A.T2_Date, [BBB]=B.T2_Date
FROM	TEMP1 AS T
	LEFT OUTER JOIN TEMP2 AS A
		 ON A.T2_T1_ID = T.T1_ID
		AND A.T2_Type = 'AAA'
	LEFT OUTER JOIN TEMP2 AS B
		 ON B.T2_T1_ID = T.T1_ID
		AND B.T2_Type = 'BBB'
GO

-- Method 1 - Create View
CREATE VIEW TEMP_V
AS
SELECT	[V_T1] = T1_ID,
	[V_AAA]=A.T2_Date,
	[V_BBB]=B.T2_Date	
FROM	TEMP1 AS T
	LEFT OUTER JOIN TEMP2 AS A
		 ON A.T2_T1_ID = T.T1_ID
		AND A.T2_Type = 'AAA'
	LEFT OUTER JOIN TEMP2 AS B
		 ON B.T2_T1_ID = T.T1_ID
		AND B.T2_Type = 'BBB'
GO

-- Method 1 - Retrieve Data from View
SELECT	T1_ID, T1_NonDate1, T1_NonDate2, V_AAA, V_BBB
FROM	TEMP1
	JOIN TEMP_V
		 ON V_T1 = T1_ID

-- Method 2 - Select data for a single row
DECLARE	@AAA	datetime,
	@BBB	datetime,
	@MyID	int

SELECT	@MyID = 1	-- The record I want - e.g. SProc parameter / Value provided by User

-- Retrive data from associated table
SELECT	@AAA = CASE WHEN T2_Type = 'AAA' THEN T2_Date ELSE @AAA END,
	@BBB = CASE WHEN T2_Type = 'BBB' THEN T2_Date ELSE @BBB END
FROM	TEMP2
WHERE	    T2_T1_ID = @MyID
	AND T2_Type IN ('AAA', 'BBB')

-- Select data from appropriate row
SELECT	T1_ID, T1_NonDate1, T1_NonDate2, [AAA]=@AAA, [BBB]=@BBB
FROM	TEMP1
WHERE	    T1_ID = @MyID


-- Drop Tables/View
DROP VIEW TEMP_V
GO
DROP TABLE TEMP1
GO
DROP TABLE TEMP2
GO

Table 'Worktable'. Scan count 0, logical reads 0
Table 'CHILD1a'. Scan count 0, logical reads 10
Table 'CHILD1'. Scan count 7, logical reads 21
Table 'Worktable'. Scan count 0, logical reads 0
Table 'CHILD2'. Scan count 5, logical reads 10
Table 'CHILD3'. Scan count 10, logical reads 30
Table 'PARENT_TABLE'. Scan count 0, logical reads 3