SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CROSS Apply or Outer Apply? DDL attached


CROSS Apply or Outer Apply? DDL attached

Author
Message
showtime-855194
showtime-855194
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 35
Here is the APPLY I have so far
Anyone have any ideas on this?

The issue with this is that everything in ValueList comes back as delimited values in one column. What I really need is each value in its own column. BUt I really need to get it to the next step. Any thoughts on how I can break them up separate rows? Basically if you look below, anything from the tables CROSS APPLIED, I want to return the items in separate columns, rather than one column. Furthermore, not every registration will have questions, relays, usat or fees...

Here is my actual query with tables and fields. Thanks!


SELECT r.Guid as RegistrationID
,a.Guid as AthleteID
,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]
,e.Fee as EntryFee
,r.OnlineFee
,TotalFee
,Convert(varchar(10),r.Date, 101) as RaceDate
,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
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
CROSS APPLY
(
SELECT
CONVERT(NVARCHAR(MAX), QuestionText) + ',' AS [text()]
, CONVERT(NVARCHAR(MAX), QuestionResponse) + ',---' AS [text()]
FROM
RegistrationQuestion re
WHERE
re.AthleteGuid = a.Guid
ORDER BY
AthleteGuid
FOR XML PATH('')
) q (questions)
CROSS APPLY
(
SELECT
CONVERT(NVARCHAR(MAX), af.Name) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX), af.Fee) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX), af.Qty) + ',---' AS [text()]
FROM
RegistrationAdditionalFee af
WHERE
af.RegistrationGuid = r.Guid
ORDER BY
AthleteGuid
FOR XML PATH('')
) _af (fees)
CROSS APPLY
(
SELECT
CONVERT(NVARCHAR(MAX), rl.LegName) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX), rl.Name) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX), rl.MedicalConditions) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX),Convert(varchar(10),rl.DateOfBirth, 101)) + ',---' AS [text()]
FROM
RegistrationRelayLeg rl
WHERE
rl.RegistrationGuid = r.Guid
ORDER BY
AthleteGuid
FOR XML PATH('')
) _rl (relay)
CROSS APPLY
(
SELECT
CONVERT(NVARCHAR(MAX), uf.Comment) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX), uf.Fee) + ',---' AS [text()]
FROM
RegistrationUSATFee uf
WHERE
uf.RegistrationGuid = r.Guid
ORDER BY
AthleteGuid
FOR XML PATH('')
) _uf (usat)
WHERE TotalFee >0
AND r.IsPaid = 1
AND r.WaiverInitials<>''
AND r.RaceGuid=@RaceGuid
ORDER BY RegistrationID, AthleteID, LName




Here is the DDL

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



Ben Teraberry
Ben Teraberry
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2975 Visits: 1199
Can you give an example of the output you want versus an example of the output you're getting? (It's not possible for me to run the code that you've provided without error.) I'm not sure what exactly you mean in terms of what it is you want to see in different columns.

└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
showtime-855194
showtime-855194
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 35
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



Mike - CI
Mike - CI
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 1155
I just took your last DDL and replaced the final select with this

SELECT Distinct 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
,_rl.relay

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
CROSS APPLY
(
SELECT
CONVERT(NVARCHAR(MAX), rl.LegName) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX), rl.Name) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX), rl.MedicalConditions) + ',' AS [text()]
,CONVERT(NVARCHAR(MAX),Convert(varchar(10),rl.DateOfBirth, 101)) + ',---' AS [text()]
FROM
#RegistrationRelayLeg rl
WHERE
rl.RegistrationGuid = r.Guid
ORDER BY
AthleteGuid
FOR XML PATH('')
) _rl (relay)
WHERE TotalFee > 0
and r.RaceGuid='04010abc-83dd-4aaf-abb1-0177eac5d562'



I think that gets what you want, but it is hard to say for sure.
showtime-855194
showtime-855194
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 35
Thanks. Thats close , but not quite it. When you run that query, do you see the last column (relay). Everything in there that is separated by the comma I need to have come back as a separate column(s). That is what I am after. What you suggested, i have already tried. That is as far as I got. If you have any other advice, I am all ears! Thanks!

HC
Ben Teraberry
Ben Teraberry
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2975 Visits: 1199
Not sure if I understand, but if you want to get values in separate columns rather than delimited in one column, why are you using XML PATH?

└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
showtime-855194
showtime-855194
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 35
You are understanding correctly. The issue is that I dont know how to do it. I realize now that XML PATH is incorrect. But, that was as far as I got. I figured I would post the latest code I had and see if someone had a way I could do it. I certainly am not stuck with XML Path. I'll use anything. Just trying to avoid nested loops! Thanks

HC
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search