Concatenate Names

Transact-SQL
1

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.

2

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

3

That worked well. Thank you.

4

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

5

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;