Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
*TSQL Challenge* Is this doable?

Answered *TSQL Challenge* Is this doable?

  • Thursday, February 14, 2013 9:27 PM
     
     
    Sign In to Vote
    0

    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:

    Participant     Email                         Phone              Role      
    William Tell      WillyTell@test.com      9549996300   Volunteer
    William Tell      WillyTell@test.com      9549996300   Staff
    William Tell      WillyTell@test.com      9549996300   Paramedic

    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!

    G.


    • Edited by g2beastie Friday, February 15, 2013 2:48 AM
    •  
     

All Replies

  • Thursday, February 14, 2013 9:33 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    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


    My blog

    • Marked As Answer by g2beastie Thursday, February 14, 2013 10:03 PM
    •  
     
  • Thursday, February 14, 2013 9:36 PM
     
      Has Code
    Sign In to Vote
    1

    Oye Guillermo,

    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,
    ~CA

    Adam

     
  • Thursday, February 14, 2013 10:11 PM
     
     
    Sign In to Vote
    0

    Naomi,

    That worked flawlessly! Thank you so much!!

    Guillermo Garcia.

     
  • Friday, February 15, 2013 1:20 AM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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