Thursday, February 14, 2013 9:27 PM
Hello! I am trying to write a query to would match the output for a grid in a web application prototype. However, the solution I have thought of works, but I am afraid it's not the best performance-wise. Allow me to explain further. I have 3 tables Person, Participants, Roles. There is a many-to-many relationship between Person and Roles with Participants as the intermediate table; so basically a person can have many roles. Now, when I join all three tables I get this output, which is expected and acceptable in most situations since each row is unique:
However, I mentioned that I am trying to write a query to return to an application to bind to a grid but the grid needs to show 1 single record per Person/Participant and if they have more than one Role, show them all in the one record divided with slashes. So for the example presented above, the desired output would be this:
Participant Email Phone Role
William Tell WillyTell@test.com 9549996300 Volunteer/Staff/Paramedic
The solution I have thought of consists of basically creating a stored procedure that will loop through each person and build the desired string of roles in a temp table: one column with the PersonID and another column that will store the Role(s) through each loop with the slashes in between already, creating said string. Once it has gone through every Person, I can use this temp table in my query and achieve 1 single record per Person showing all their roles in the same field.
Now, is there a better way to do this?! Your thoughts are greatly appreciated!
- Edited by g2beastie Friday, February 15, 2013 2:48 AM
Thursday, February 14, 2013 9:33 PMModerator
Assuming you have SQL Server 2005 and up you can simply concatenate all roles together like this
select P.Name, P.email, P.Phone, stuff((select '/' + Role from Roles r inner join Participants Part ON r.RoleId = Part.RoleId where Part.PersonId = P.PersonID FOR XML PATH('')),1,1,'') as [All Roles] from Person P
For every expert, there is an equal and opposite expert. - Becker's Law
- Marked As Answer by g2beastie Thursday, February 14, 2013 10:03 PM
Thursday, February 14, 2013 9:36 PM
I didn't actually test this, but give the following a try:
DECLARE @result varchar(max); SELECT Participant , Email , Phone , @result + Role + ',' FROM Person per INNER JOIN Participants part ON per.key = part.key INNER JOIN Roles rol ON part.key = rol.key GO
Good Luck Bro,
Thursday, February 14, 2013 10:11 PM
That worked flawlessly! Thank you so much!!
Friday, February 15, 2013 1:20 AM
Have you ever read a book on RDBMS or tiered architectures? You do display formatting in the front end of RDBMS. Concatentation would violate 1NF. This is one way rows are not records. And columns are not fields, too. Your mindset is still in sequential file system!
There are several common kludges that bad programmers use to do this. Currently XML is popular; before now it was a T-SQL flaw in the UPDATE statement; before that it was the temp table you re-discovered.
Throw the result set to a report writer.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Friday, February 15, 2013 2:19 PM
CELKO, I appreciate your reply. This didn't seem conventional to me either, but I'm trying to make things easier for the developers I'm working with. I'm relatively new to the application development side of SQL so I actually don't know what the best practice is for this scenario. You're saying to pass the result set to a report writer. Would you mind elaborating a bit more on this solution? How would that integrate with an object like a grid?
Leroy G. Brown