sql - Merge multiple rows in data to show only a single row in the result -

I have a stored procedure that takes 1 parameter, an ID number (systudentid).

The process gives 3 rows: Academic Consultant (AC), Financial Advisor (FC), and Entry Counselor (EC) with relevant contact information; 3 different people

Some students have AC and FCs who are the same person, but the query will still return 3 rows.

  AdvisorType | AdvisorLlastName | AdvisorFirstName | (Other data). Systaffid a doe john ..... 12345 a doe john ..... 12345 a smith jen ..... 45678   
 In my code I argued (and how, I I am a newbie with SBL) so that when the systudentid passed in the process, to identify the same person for both the AC and FC, it will display the results like this.  
 The advisory type has been changed to "SSA" and only one out of the record for the Double-Duty Counselor has been returned.  
  AdvisorType | AdvisorLlastName | AdvisorFirstName | (Other data). Systaffid Sarva Shiksha Abhiyan's DOE John ...... 12345 A Smith Jen ...... 45678   
 Here is my selection statement:  
  select SyStaffGroup.Descrip as AdvisorType, as SyStaff.ListName Slahkarlalenam, SyStaff. as a first-name consultants first name, advisory phone as SyStaff.Phone, SyStaff.Ext aS consultant detail, SyStaff.Email consultant email, as Sistf .SyStaffID include SyStaffID from SyStaff (NOLOCK) include SyAdvisorByEnroll.SyStaffID = SyStaff.SyStaffID SyStaffGroup (NOLOCK) on nominating SyAdvisorBy (NOLOCK) are as SyStaffGroup.SyStaffGroupID = SyAdvisorByEnroll.SyStaffGroupID AdEnroll (NOLOCK) Join Join AdEnroll.AdEnrollID = SyAdvisorByEnroll.AdEnrollID SyStudent (NOLOCK) AdEnroll.SyStudentID = SyStudent. SELECT adNnrollID and SyAdvisorByEnroll from SystudentId WHERE SyStaff.Active = 1 --and --syadvisorbyenroll.adenrollid = (dbo.fn_student_enrollment_activeenrollmentlist (--systudentid) - adresolid -) and adEnroll.adEnrollID IN (debu.fn_stutton_edirected_activanolement_list (systdent id)) in AdvisorModule ( 'e', ​​'FA') and SyStaffGroup.Descrip ( 'AC - academic Counselor', 'FC - financial advisor', 'EC -. Admiral Counselor' with reggae) select UNION DISTINCT 'sign Counselor 'as AdvisorType, SyStaff.LastName AdvisorLastName, SyStaff.FirstName As AdvisorFirstName, SyStaff.Phone as AdvisorPhone, SyStaff.Ext AdvisorExtention as, as SyStaff.eMail involved as AdvisorEMail, SyStaff.SyStaffID SyStaffID within systudent which SyStudent.AMREpID on AmRep = AmREp.AMREpid SyStaff.SyStaffID inner = AmRep.AmRepID where Systudent.SYStudentid = @systudentid SyStaff JOIN   
 can any way indicated or suggested that I try either or Google (I searched But the results are very useful if I knew what to see) very much appreciate Will be gone.   
 
 
 You can tell which student has the same advisory to fill multiple positions, and accordingly You can add nested subversion to adjust type selection. Here are the excerpts of the above references to the query:  
  Select the case when (mutiples.SyStaffID is NOT NULL) then the 'Sarva Shiksha Abhiyan' remaining SyStaffGroup.Descrip as the AdvisorType - Add other columns removed from SyStaff (NOLOCK) in SyAdvisorByEnroll (NOLOCK) SyAdvisorByEnroll.SyStaffID = SyStaff.SyStaffID LEFT JOIN (Select Sytetaffid, AdEnrollID from SyAdvisorByEnroll Group by SyAtaffID, AdEnrollID Hosting COUNT (DISTINCT SyStaffGroupID)> 1 ) multiplier on multiples.SyStaffID = SyAdvisorByEnroll.SyStaffID and multiples .AdEnrollID = SyAdvisorByEnroll.AdEnrollID - balance query omitted   
 this Switch to be one or two mistakes, because you are all advisors / Anrolli pairs nested subkey, "multiplier" did not include table schema, where the consultant is in many groups. If you have a mailing entry in the Nested Subquiri, then you get added against it and you can adjust the last type of selection to "SSA".  
 An important note: In writing, a qualified consultant / Enrollee pair will not, however, be the final result, because you are using a union in this query, which filters the duplicate, even if Only they will be present in one half union. If you change it to a union or to completely eliminate the union, you will need to add DISTINCT to the top of the query, like:  
  Select a different case (mutiples. SyStaffID is not) ...