Group all items per ID in one row
-
Ok, so after alot of time, i finally got enough help to get me to this point and I am hoping that someone can get me over the hump. This query returns me data in one row per athleteID as long as the row_number equals the first row. Now, that makes sense with the way the query is written. But I need to take it further
The issue is that for each of those Left Join tables, there could be (and usually is) more than one row.
So I could have one athleteID and they could have many rows in the RegistrationRelayLeg or RegistrationQuestion (or any other left join table), but the problem is that this query is only giving me the first hit on that row.
So, per AthleteID, I could have an infinite number of
,rrl.LegName
,rrl.Name as RelayName
,rrl.MedicalConditions
,rrl.DateOfBirth
My query gives me just the first one. Is there a way I can modify this so that I can all the rrl columns (or any of the LEFT JOINED tables) in ONE row? Thanks
SELECT RegistrationID
,AthleteID
,FName
,LName
,Sex
,Addr
,City
,ST
,Zip
,Medical
,Email
,DayPhone
,EveningPhone
,USATNumber
,TShirtSize
,Div
,Paytype
,[Event]
,RaceDate
,TotalFee
,OnlineFee
,TotalDiscount
,FeeName
,Qty
,Fee
,Total
,LegName
,RelayName
,MedicalConditions
,DateOfBirth
--,DateOfBirth
,QuestionText
,QuestionResponse
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.Guid 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'
)t
WHERE rn=1
ORDER BY RegistrationID, AthleteID, LName
-
You can, but if there are going to be a variable number of rows to be converted into columns, you will have to do it using dynamic sql. "Infinite" is problematic, because you can't have an infinite number of columns in a table. I will advise against doing this because if the number of rows is large it is frankly going to look like hell and be almost unworkable to have an output table that reads horizontally like this:
FeeName1
Qty1
Fee1
Total1
LegName1
FeeName1
Qty2
Fee2
Total2
LegName2
... etc ect
FeeName999
Qty999
Fee999
Total999
LegName99
Why do you need it pivoted this way? It would read much easier to just have a header line with the non-repeated info, and then a list of the repeated items appearing row-by-row below it.
Can you show us some sample input and expected outputs using the schema you have described? A script to populate the input tables with sample rows would be best.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Thanks for taking the time to look. I said Infinite because I don't know the number rows that could ultimately be returned for each LEFT JOIN. In practice, it should be no more than 10 per Left JOIN though. For the reason, I must go with the ubiquitous answer of "That is what the client wants"
They want to see in the HTML (a rad grid) and export it into Excel and CSV as well. At that point I figured its better to do this in the SQL rather than on the client.
So, as requested, here is a script that will populate the tables and let you see the real output. The results you will get are all from ONE registration. You should get five rows returned in the SQL. Three rows are for the related rows in RegistrationRelayleg and two rows are from related rows in RegistrationQiestion.
More or less, I want all that to appear in one row, but I obviously dont want to repeat items like Athlete.FirstName, Athlete.LastName, etc...Only the differing rows from the LEFT JOIN tables should be repeated.
The image is a basic mockup of what the output should be (its not complete, but you get the idea). Thanks 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
-
Mark, the problem is that SQL is not the right tool for doing things like not showing the repeated values. It is going to deliver a result set that includes values for all columns. Yes I can imagine putting the results in a work table and blanking out the columns, but you are putting a lot of work on the server that should be spread out among the clients. Client side tools such as report writers are BETTER at doing that kind of manipulation than SQL. What you are asking for will make your client side apps handle a varying number of columns. Let the database just serve up data, that's what it's good at. This is my strong recommendation in this instance.
I will post up some code this later to show you a dynamic SQL cross tab, but this is definitely not the way I would go in this instance.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
I am willing to do this in the client, but frankly, I have not been able to figure out how. I dont really have the option of an y report writer. I would have to do in manually in the ASP.NET (C#)
But, I will take any method at this point. I have been at this for a long time so any guidance or help si much appreciated.
HC
-
Anyone want to give this a crack? I would appreciate it. Thanks
-
Hi;
From the query what you have posted, the SQL is doing a great job in fetching the data to this level. If you look in to the data what the output it returns... (the SQL query output)
You can see that in each row the data is repeating, except, these two columns
,rq.QuestionText
,rq.QuestionResponse
which has diff data. Comment these two columns and use distinct keyword, you will see three rows, with different LegName and RelayName.
I would suggest, two things
A:
Using C# you can manipulate the grid to show first common and unique values and put some kind of "+" mark, and on click it can expand and shows the data which is repeating... as a para, one after the other. (This will be structured)
B:
Create a function in the SQL, where you will collect ONLY those values which can be more than one for each Leg or Relay name, concatinate all those values in a string and you can use carriage return for new line and align them as needed. This function can take the param of the Athlete ID and returns a bundled string of values. you can simply call this function in the sql statement and pass the column ref to it and in the output there will be one column with all the other details stuffed in as one.
I had a scenario B and it worked for me. Hope this helps.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply