Group all items per ID in one row

Question

Post reply

Group all items per ID in one row

showtime-855194

SSC Veteran

Points: 270
More actions
February 11, 2010 at 2:55 pm

#219103

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 1

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

showtime-855194 SSC Veteran Points: 270 More actions · Answer 2

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

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 3

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

showtime-855194 SSC Veteran Points: 270 More actions · Answer 4

showtime-855194

SSC Veteran

Points: 270

February 12, 2010 at 2:32 pm

#1118314

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

showtime-855194 SSC Veteran Points: 270 More actions · Answer 5

showtime-855194

SSC Veteran

Points: 270

February 17, 2010 at 2:48 pm

#1120493

Anyone want to give this a crack? I would appreciate it. Thanks

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

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.