Concatenate Names

This is my current query.

SELECT p.LastName AS 'Last Name', p.FirstName AS 'First Name', p.MInitial AS 'MI', p.GraduatingYear AS 'Graduating Year', Referrals.ReferralDate AS 'Referral Date', p.PatientID FROM tblDimPatients AS p LEFT OUTER JOIN (SELECT PatientID, MIN(ReferralDate) AS ReferralDate FROM tblFactReferrals GROUP BY PatientID) AS Referrals ON p.PatientID = Referrals.PatientID LEFT OUTER JOIN tblDropOptions AS o ON o.doOption = p.GraduatingYear WHERE (p.Status = 'Referral') ORDER BY o.doOrder, 'Referral Date'

This displays the LastName, FirstName, and MInitial as separate fields with corresponding headers. I need them to display as one field with the header Patient.

Thank you for any help.

Hi

Try:

SELECT p.LastName + ', ' + p.FirstName + ' ' + p.MInitial AS 'Patient', p.GraduatingYear AS 'Graduating Year', Referrals.ReferralDate AS 'Referral Date', p.PatientID FROM tblDimPatients AS p LEFT OUTER JOIN (SELECT PatientID, MIN(ReferralDate) AS ReferralDate FROM tblFactReferrals GROUP BY PatientID) AS Referrals ON p.PatientID = Referrals.PatientID LEFT OUTER JOIN tblDropOptions AS o ON o.doOption = p.GraduatingYear WHERE (p.Status = 'Referral') ORDER BY o.doOrder, 'Referral Date'

Thanks

That worked well. Thank you.

Watch for null names (mainly with MInitial) as if you concatenate a null the whole becomes null.

To avoid null concatenation nulling the expression:

SELECT 
     p.LastName + ', ' + p.FirstName + IsNull(' ' + p.MInitial, '') AS 'Patient'
   , p.GraduatingYear AS 'Graduating Year'
   , Referrals.ReferralDate AS 'Referral Date'
   , p.PatientID 
FROM 
   tblDimPatients AS p 
LEFT OUTER JOIN 
   (
   SELECT 
        PatientID
      , MIN(ReferralDate) AS ReferralDate 
   FROM tblFactReferrals GROUP BY PatientID
   ) AS Referrals 
ON p.PatientID = Referrals.PatientID 
LEFT OUTER JOIN 
   tblDropOptions AS o 
ON o.doOption = p.GraduatingYear 
WHERE 
   p.Status = 'Referral' 
ORDER BY 
     o.doOrder
   , Referrals.ReferralDate;Date;