Try this. This is the results I get.
Then scroll all the way to the right. You will see Four Columns (LegName, RelayName, medicalCOnditions, DOB)
What I really NEED is instead of three rows, I want this all in ONE tow. So in this instance, i would have LegName, RelayName, medicalCOnditions, DOB repeated three times horizontally with the right data in them. Using For XML and cross apply, I can get them in a comma list, but all in one field. But I need them in separate fields.
The kicker is that I don't have a predetermined number of RelayLegs. I could have three, I could have five. AND not every registration actually has them! So, any thoughts?
CREATE TABLE [dbo].[#Registration](
[Guid] [uniqueidentifier] NOT NULL,
[AccountGuid] [uniqueidentifier] NOT NULL,
[RaceGuid] [uniqueidentifier] NOT NULL,
[Date] [datetime] NULL,
[TotalFee] [money] NOT NULL,
[WaiverInitials] [nvarchar](50) NULL,
[OnlineFee] [money] NOT NULL,
[PayByCheck] [bit] NOT NULL,
[IsPaid] [bit] NOT NULL,
[PaperRegistration] [bit] NOT NULL
)
INSERT INTO #Registration
SELECT '7ef8b580-5166-4ae2-b1fb-447d15f50bc5', '914a0a9e-0b3c-46e3-ad96-8920717d8081',
'04010abc-83dd-4aaf-abb1-0177eac5d562'
,'2010-02-10 10:59:46.490', 2047.9950, 'HC', 0.0000, 0, 1, 0
CREATE TABLE [dbo].[#Event](
[Guid] [uniqueidentifier] NOT NULL,
[RaceGuid] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Fee] [money] NOT NULL,
[IsRelay] [bit] NOT NULL,
[MaxEntrants] [int] NOT NULL,
[FromDate] [date] NULL,
[ToDate] [date] NULL,
[DisplayOrder] [int] NOT NULL,
[Deleted] [bit] NOT NULL,
[Disabled] [bit] NOT NULL
)
INSERT INTO #Event
SELECT 'ba5dcb0c-b726-4f76-bdc0-1bffe6ca6697', '04010abc-83dd-4aaf-abb1-0177eac5d562',
'Test Relay Event', 125.0000, 1, 4, NULL, NULL, 2, 0, 0
UNION ALL
SELECT '75843b81-662e-4b1f-b73e-2e3dd8be184e', '04010abc-83dd-4aaf-abb1-0177eac5d562',
'Triathlon Boot Camp', 575.0000, 0, 100 ,NULL, NULL, 0, 0, 0
CREATE TABLE [dbo].[#Athlete](
[Guid] [uniqueidentifier] NOT NULL,
[AccountGuid] [uniqueidentifier] NOT NULL,
[Firstname] [nvarchar](50) NOT NULL,
[Middlename] [nvarchar](50) NULL,
[Lastname] [nvarchar](50) NOT NULL,
[Email] [nvarchar](max) NOT NULL,
[DayPhone] [nvarchar](50) NOT NULL,
[EveningPhone] [nvarchar](50) NOT NULL,
[MobilePhone] [nvarchar](50) NULL,
[Address1] [nvarchar](200) NOT NULL,
[Address2] [nvarchar](200) NULL,
[Address3] [nvarchar](200) NULL,
[City] [nvarchar](200) NULL,
[State] [nvarchar](2) NOT NULL,
[Zip] [nvarchar](15) NOT NULL,
[Gender] [nvarchar](50) NULL,
[DateOfBirth] [date] NULL,
[IsAccountOwner] [bit] NOT NULL,
[MedicalConditions] [varchar](500) NULL,
[Country] [nvarchar](150) NULL
)
INSERT INTO #Athlete
SELECT 'bec2a987-20fd-4279-bd75-528686d0ab7c'
, '914a0a9e-0b3c-46e3-ad96-8920717d8081'
, 'jeremy'
, ''
, 'T'
, 'test@lin-mark.com'
, '856-555-0010'
, '856-555-0010'
, '856-555-0010'
, '123 test'
, '123 Test'
, '123 Test'
, 'Mantua'
, 'NJ'
, '09980'
, 'M'
, '1982-01-26'
, 0
, 'none'
, 'United States of America'
CREATE TABLE [dbo].[#RegistrationEvent](
[Guid] [uniqueidentifier] NOT NULL,
[RegistrationGuid] [uniqueidentifier] NOT NULL,
[RaceGuid] [uniqueidentifier] NOT NULL,
[EventGuid] [uniqueidentifier] NULL,
[AthleteGuid] [uniqueidentifier] NOT NULL,
[TShirtSize] [nvarchar](100) NULL,
[Division] [nvarchar](100) NULL,
[IsSelected] [bit] NOT NULL,
[RaceDetailsCompleted] [bit] NOT NULL,
[USATNumber] [nvarchar](9) NULL,
[Paid] [bit] NOT NULL
)
INSERT INTO #RegistrationEvent
SELECT '87c9a8c3-9cd9-4341-8795-b114a26df8f0', '7ef8b580-5166-4ae2-b1fb-447d15f50bc5',
'04010abc-83dd-4aaf-abb1-0177eac5d562', 'ba5dcb0c-b726-4f76-bdc0-1bffe6ca6697' ,
'bec2a987-20fd-4279-bd75-528686d0ab7c',
'small', '5K Competitve Walk Age Grouper', 1, 1, NULL, 0
CREATE TABLE [dbo].[#RegistrationRelayLeg](
[Guid] [uniqueidentifier] NOT NULL,
[RegistrationGuid] [uniqueidentifier] NOT NULL,
[RegistrationEventGuid] [uniqueidentifier] NOT NULL,
[RelayLegGuid] [uniqueidentifier] NOT NULL,
[AthleteGuid] [uniqueidentifier] NOT NULL,
[LegName] [nvarchar](200) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[DateOfBirth] [date] NOT NULL,
[USATNumber] [nvarchar](50) NULL,
[AcceptWaiver] [bit] NOT NULL,
[EmergencyContactName] [nvarchar](200) NOT NULL,
[EmergencyContactNumber] [nvarchar](50) NOT NULL,
[EmailAddress] [nvarchar](200) NULL,
[TshirtSize] [nvarchar](50) NULL,
[MedicalConditions] [nvarchar](500) NULL
)
INSERT INTO #RegistrationRelayLeg
SELECT '82d030d8-ec2f-4992-92ff-5349b5ffa67d', '7ef8b580-5166-4ae2-b1fb-447d15f50bc5',
'876d80b9-0399-4854-92c2-da0a359dd995', 'bb30f1f6-c358-45c5-849f-0b311c189f0d',
'bec2a987-20fd-4279-bd75-528686d0ab7c', 'Cyclist', 'Cyclist', '1945-04-04', NULL, 1,
'test', 'test', 'test', NULL, 'test'
UNION ALL
SELECT '36a9e472-4fac-456e-ba93-944c9e52d8cb', '7ef8b580-5166-4ae2-b1fb-447d15f50bc5',
'876d80b9-0399-4854-92c2-da0a359dd995', 'a5ddb0ce-b4ed-49e6-954b-a9013cbf9258',
'bec2a987-20fd-4279-bd75-528686d0ab7c', 'Swimmer', 'Swimmer',
'1987-05-03', NULL, 1, 'none', '555-555-5555', 'hcwork@verizon.net', NULL, 'none'
UNION ALL
SELECT '6cc8b51f-299a-44bb-949e-e9a88142abe7', '7ef8b580-5166-4ae2-b1fb-447d15f50bc5',
'876d80b9-0399-4854-92c2-da0a359dd995', 'b6652d7c-c883-4531-bb0d-f4d8a4793da6',
'bec2a987-20fd-4279-bd75-528686d0ab7c', 'Runner', 'Runner', '1984-01-01', NULL, 1,
'none', 'none', 'none', NULL, 'none'
CREATE TABLE [dbo].[#RegistrationQuestion](
[Guid] [uniqueidentifier] NOT NULL,
[RaceGuid] [uniqueidentifier] NOT NULL,
[AthleteGuid] [uniqueidentifier] NOT NULL,
[RegistrationGuid] [uniqueidentifier] NOT NULL,
[QuestionGuid] [uniqueidentifier] NOT NULL,
[QuestionText] [nvarchar](300) NOT NULL,
[QuestionType] [nvarchar](50) NOT NULL,
[QuestionResponse] [nvarchar](1000) NULL,
[DisplayOrder] [int] NOT NULL
)
INSERT INTO #RegistrationQuestion
SELECT 'e41befbc-b4d3-4a65-962d-de81cd5a89fd', '04010abc-83dd-4aaf-abb1-0177eac5d562',
'bec2a987-20fd-4279-bd75-528686d0ab7c', '7ef8b580-5166-4ae2-b1fb-447d15f50bc5',
'479bd03e-1478-4c73-a52c-a6aebbed7809', 'Do You Like Tris', 'True/False',
'yes', 1
UNION ALL
SELECT '003a7f2b-2aea-44a8-aa6e-f10c474cc029', 'c22618ef-d2df-4cb2-9263-df97d0ebb0c2',
'bec2a987-20fd-4279-bd75-528686d0ab7c', '7ef8b580-5166-4ae2-b1fb-447d15f50bc5',
'c5bae87f-ea7c-4438-a88e-f988575c00b2,',
'If You Are Part Of A Team Then Enter Your Team Name (Type NA if not applicable)', 'Fill in', 'ee', 3
SELECT ROW_NUMBER() OVER(PARTITION BY re.AthleteGuid ORDER BY a.DateOfBirth DESC) AS rn,
r.Guid as RegistrationID
,a.Guid as AthleteID
,a.Firstname as FName
,a.LastName as LName
,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]
,r.Date as RaceDate
,TotalFee
,r.OnlineFee
--,IsNull(rd.TotalDiscount,0.00) as TotalDiscount
--,ra.Name as FeeName
--,ra.Qty
--,ra.Fee
--,ra.Total
,rrl.LegName
,rrl.Name as RelayName
,rrl.MedicalConditions
,rrl.DateOfBirth
--,rq.QuestionText
--,rq.QuestionResponse
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
LEFT JOIN #RegistrationRelayLeg rrl ON rrl.AthleteGuid = a.Guid
--LEFT JOIN #RegistrationQuestion rq ON rq.AthleteGuid = a.Guid
--LEFT JOIN RegistrationAdditionalFee ra ON ra.RegistrationGuid = r.Guid
WHERE TotalFee >0
and r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562'
DROP TABLE #Registration
DROP TABLE #Event
DROP TABLE #Athlete
DROP TABLE #RegistrationEvent
DROP TABLE #RegistrationRelayLeg
DROP TABLE #RegistrationQuestion