Select Statement

1

Hi,

I have the following SQL statement and it brings 2 records per practice. One with the Practice Manager details and the second with the Lead GP details. All other columns are the same.

I would like to have instead ONLY one record with both the Practice Manager and Lead GP details. Any help will be appreciated

SELECT     TOP (100) PERCENT Details.PracticeNumber, Picklist.ContactTitles.Description AS Title, Contacts.FirstName, Contacts.LastName, Contacts.Role, 
                      Addresses.PracticeName, Addresses.Address1, Addresses.Address2, Addresses.Address3, Addresses.Town, Addresses.County, Addresses.PostCode, 
                      Contacts.Email, Details.ContributionStatus
FROM         Practice.AllCurrentDetails AS Details INNER JOIN
                      Practice.AllCurrentContacts AS Contacts ON Details.CetoPracticeId = Contacts.CetoPracticeId LEFT OUTER JOIN
                      Practice.AllCurrentAddresses AS Addresses ON Addresses.CetoPracticeId = Details.CetoPracticeId LEFT OUTER JOIN
                      Picklist.SystemProviders ON Details.SystemProvider = Picklist.SystemProviders.SystemProviderId LEFT OUTER JOIN
                      Picklist.ContactTitles ON Contacts.Title = Picklist.ContactTitles.ContactTitleId
WHERE     (Details.SystemProvider = 1) AND (Details.ContributionStatus <> 4) AND (Contacts.Role = 'Practice Manager' OR
                      Contacts.Role = 'Lead GP')
ORDER BY Details.PracticeNumber

Thanks

2 
WITH Contacts
AS
(
	SELECT C.CetoPracticeId
		,MAX(CASE WHEN C.[Role] = 'Practice Manager' THEN C.FirstName END) AS FirstNamePM
		,MAX(CASE WHEN C.[Role] = 'Practice Manager' THEN C.LastName END) AS LastNamePM
		,MAX(CASE WHEN C.[Role] = 'Practice Manager' THEN C.Email END) AS EmailPM
		,MAX(CASE WHEN C.[Role] = 'Practice Manager' THEN T.[Description] END) AS TitlePM
		,MAX(CASE WHEN C.[Role] = 'Lead GP' THEN C.FirstName END) AS FirstNameGP
		,MAX(CASE WHEN C.[Role] = 'Lead GP' THEN C.LastName END) AS LastNameGP
		,MAX(CASE WHEN C.[Role] = 'Lead GP' THEN C.Email END) AS EmailGP
		,MAX(CASE WHEN C.[Role] = 'Lead GP' THEN T.[Description] END) AS TitleGP
	FROM Practice.AllCurrentContacts C
		LEFT JOIN Picklist.ContactTitles T
			ON C.Title = T.ContactTitleId
	WHERE C.[Role] IN ('Practice Manager','Lead GP')
	GROUP BY C.CetoPracticeId
)
SELECT D.PracticeNumber
	,C.TitlePM, C.FirstNamePM, C.LastNamePM, C.EmailPM
	,C.TitleGP, C.FirstNameGP, C.LastNameGP, C.EmailGP
	,A.PracticeName, A.Address1, A.Address2, A.Address3, A.Town, A.County, A.PostCode
    ,D.ContributionStatus
FROM Practice.AllCurrentDetails D
	JOIN Contacts C
		ON D.CetoPracticeId = C.CetoPracticeId
	LEFT JOIN Practice.AllCurrentAddresses A
		ON D.CetoPracticeId = A.CetoPracticeId
ORDER BY D.PracticeNumber;
3

Thank you