RE: CROSS Apply or Outer Apply? DDL attached

SSC Veteran

Points: 280

April 13, 2010 at 12:46 pm

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'

, '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