CROSS Apply or Outer Apply? DDL attached

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CROSS Apply or Outer Apply? DDL attached

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2010-04-02 : 19:37:55

I have data that comes out to me like this...THIS IS JUST AN EXAMPLE so you can see the data. If you run both SQL statements you will see what I get, then what I want. There is no fixed number of items per RegID, so I cannot hard code anything.



SELECT 1 as RegID, 'Water' as Name, '2.00' as Fee, '3' as Quantity, NULL as Question
UNION ALL
SELECT 1 as RegID,'Food' as Name, '5.00' as Fee, '2' as Quantity, NULL as Question
UNION ALL
SELECT 1 as RegID, NULL, NULL,NULL,'My Name is Bob' as Question1
UNION ALL
SELECT 1 as RegID, NULL, NULL,NULL,'I like to swim' as Question2

I am trying to find a way to retrieve it like this

SELECT 1 as RegID, 'Water' as Name, '2.00' as Fee, '3' as Quantity,'Food' as Name, '5.00' as Fee, '2' as Quantity,
'My Name is Bob' as Question1,'I like to swim' as Question2

If someone would be able to show me an example of how I can get data like this, it would be awesome. I have heard about CROSS and Outer Apply, but I was never able to get it to work...

If someone is really crazy, here is my DDL if you my actual tables and fields. Thanks so much for taking a look

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 02:33:24

one way of doing this is:-


SELECT * INTO #Tbl
FROM
(
SELECT 1 as RegID, 'Water' as Name, '2.00' as Fee, '3' as Quantity, NULL as Question
UNION ALL
SELECT 1 as RegID,'Food' as Name, '5.00' as Fee, '2' as Quantity, NULL as Question
UNION ALL
SELECT 1 as RegID, NULL, NULL,NULL,'My Name is Bob' as Question1
UNION ALL
SELECT 1 as RegID, NULL, NULL,NULL,'I like to swim' as Question2
)t





select t.RegID,
STUFF((SELECT ';' + COALESCE(' Name: '+ Name,'') + COALESCE(' Fee: '+ Fee,'') + COALESCE(' Quantity: '+ Quantity,'') +
 COALESCE(' Question: '+ Question,'') FROM #Tbl WHERE RegID=t.RegID FOR XML PATH('')),1,1,'') AS ValueList
FROM (SELECT DISTINCT RegID FROM #Tbl)t

DROP TABLE #Tbl


output
--------------------------------------------------------------
RegID       ValueList
----------- ---------------------------------------------------------------------------------------------------------------------------------
1            Name: Water Fee: 2.00 Quantity: 3; Name: Food Fee: 5.00 Quantity: 2; Question: My Name is Bob; Question: I like to swim

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Harry C
Posting Yak Master

148 Posts

Posted - 2010-04-04 : 22:32:33

Thanks visakh16. 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. I achieved what you are suggesting using CROSS APPLY. 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

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


   -- Insert statements for procedure here
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 05:02:53

But will you be definite on number of associated values coming for each field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Harry C
Posting Yak Master

148 Posts

Posted - 2010-04-06 : 14:46:53

Well, I always know that questions for example will be questions and answer. But I don't know how many question/answer groups are actually in the list. There could be 1 or 5. Or anything really, though probably nor more than five or so.

Any thoughts? Thanks so much for taking a look

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 15:17:08

I edited the first two posts (Harry and visakh) to fix the wide page. Please ensure your posts aren't so wide in future posts.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Harry C
Posting Yak Master

148 Posts

Posted - 2010-04-07 : 00:08:21

Sorry tkizer. I will make sure in the future. Just really itching the get an answer to this one. Its been months I have been working on it. And I have not found a good solution...almost afraid I may have to resort to dreaded loops! Thanks

HC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 00:26:07

quote:
Originally posted by Harry C

Well, I always know that questions for example will be questions and answer. But I don't know how many question/answer groups are actually in the list. There could be 1 or 5. Or anything really, though probably nor more than five or so.

Any thoughts? Thanks so much for taking a look

then you need to use dynamic sql, as you're never definite on number of columns to be created

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Harry C
Posting Yak Master

148 Posts

Posted - 2010-04-07 : 10:21:38

any examples on the how? I really have been stuck on this one. Thanks

HC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 13:47:09

something like

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources