SELECT r.Guid as RegistrationID
,a.Guid as AthleteID
,CASE 
	WHEN r.WaiverInitials = '' THEN '.us'
	ELSE '.com'
	END AS SiteReg
,re.RegEvtKey as RegEvtKey
,a.AthKey as AthKey
,a.Firstname as FName
,a.LastName as LName
,Convert(varchar(10),a.DateOfBirth, 101) as DOB
,a.Gender as Sex
,a.Address1 as Addr
,a.City
,a.[State] as ST
,a.Zip
,a.MedicalConditions as Medical
,a.Email
,a.DayPhone
,a.EveningPhone
,re.USATNumber
,re.TShirtSize
,re.Division as Div
, CASE r.PaperRegistration
	WHEN 1 THEN 'Paper'
	ELSE
		CASE r.PayByCheck
			WHEN 1 THEN 'Check'
			ELSE 'CC'
			END
	END as Paytype
,e.Name as [Event]
,IsNull(re.Fee,e.Fee) as EntryFee
,r.OnlineFee
,TotalFee
,Convert(varchar(10),r.Date, 101) as RegDate
,IsNull(rd.TotalDiscount,0.00) as TotalDiscount
/*, LEFT(q.questions, LEN(q.questions)-4) as questions
, LEFT(_af.fees, LEN(_af.fees)-4) as extras
, LEFT(_uf.usat, LEN(_uf.usat)-4) as usat
, LEFT(_rl.relay, LEN(_rl.relay)-4) as relays
*/
Into #Test
FROM Registration r
INNER JOIN RegistrationEvent re ON re.RegistrationGuid = r.Guid
INNER JOIN [Event] e ON e.Guid = re.EventGuid
INNER JOIN Athlete a ON a.AccountGuid = r.AccountGuid AND a.Guid = re.AthleteGuid
LEFT JOIN RegistrationDiscount rd ON rd.RegistrationGuid = r.Guid
WHERE TotalFee >0 
AND r.IsPaid = 1
--AND r.WaiverInitials<>''
AND r.RaceGuid=@RaceGuid
ORDER BY RegistrationID, AthleteID, LName
END

DECLARE @SQL nvarchar(500)
DECLARE @AddFeeGuid uniqueidentifier
DECLARE @Name varchar(100)
DECLARE @Qty varchar(100)
DECLARE @Fee varchar(100)
--DECLARE @SQL nvarchar(500)

DECLARE my_cursor CURSOR FOR
SELECT Guid, Name, Fee
FROM AdditionalFee WHERE RaceGuid=@RaceGuid
ORDER BY DisplayOrder, Name

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @AddFeeGuid,@Name,@Fee

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL  = 'ALTER Table #test
				 ADD [' + @Name + '] nvarchar(300) '	
	EXEC(@SQL)	
	
	SET @SQL  = 'ALTER Table #test
				 ADD [' + @Name + '_' + @Fee + '] nvarchar(300) '	
	EXEC(@SQL)		

	SET @SQL  = 'UPDATE #Test
			SET [' + @Name + '] = cast(ra.Qty as nvarchar(10)) ' +
			' ,[' + @Name + '_' + @Fee + '] = cast(ra.Fee as nvarchar(10)) ' +
			' FROM #Test ' +
			' INNER JOIN RegistrationAdditionalFee as ra ON ra.RegistrationGuid=RegistrationID ' +
			' AND ''' + cast(@AddFeeGuid as nvarchar(100)) + '''= ra.AdditionalFeeGuid'
					
	PRINT @SQL
	EXEC(@SQL)
	FETCH NEXT FROM my_cursor
	INTO @AddFeeGuid,@Name,@Fee
END
CLOSE my_cursor
DEALLOCATE my_cursor


SELET * FROM #Test