SQL - field concatenation, based on variable

Question

Hi, I have a need to build a string from Last Name, First Name, Middle Initial according to the following rules:

1. If the Last Name is unique, just return the Last Name
2. If the Last Name isn't unique, but the first letter of the First Name is unique, return Last Name + first letter of First Name
3. If the Last Name and first letter of the First Name are not unique, return the Last Name + first letter of First Name + Middle Initial.

For example, the table might be:

   MDC   MDLast      MDFirst     MDInit
    3    Jones       Fred         A    
    21   Smith       Sam          D  
    32   Brown       Tom          E  
    42   Brown       Ted          A  
    55   Smith       Al           D  

The query should return:

MDC  MDFormattedName    
3    Jones  
21   Smith S  
32   Brown TE  
42   Brown TA  
55   Smith A  

I've written up a query that almost works, but it is using several nested queries, and will still need several more to (possibly) make a workable solution, and is so inefficient. I'm sure there is a 'proper' way to implement this (for SQL Server 2005, BTW).

This is what I've got so far. It doesn't work, due to the aggregations I lose the IDs can can't do the final join to get ID/Name pairs.

select 
    CASE
        WHEN CountLastFirst > 1 THEN
            CASE WHEN MDInit IS NOT NULL  THEN MDLastFirst + LEFT(MDInit,1) ELSE MDLastFirst END
        WHEN CountLastFirst = 1 AND CountLast > 1 THEN MDLastFirst
        ELSE MDLast
    END as MDName

FROM

(

select x.MDLast, CountLast, MDLastFirst, CountLastFirst FROM
(
select   MDLast,Count(MDLast) as CountLast FROM
MDList
GROUP BY MDLast) as x

INNER JOIN
(select MDLast,   MDLastFirst,Count(MDLastFirst) as CountLastFirst FROM
(
select MDLast,
MDLast + ' ' + LEFT(MDFirst,1) as MDLastFirst
From MDList
) as a
GROUP BY MDLastFirst, MDLast) as y ON x.MDLast = y.MDLast
) as z

Answer 1

Have you considered performing this operation in your application instead of directly in an SQL statement? Unless you have a good reason to do this directly in SQL, this is almost always the preferable approach for situations like this.

Answer 2

Assuming a table name of MDCTable, this should work:

SELECT MDCTable.MDC,
    CASE MDCCount.NameCount 
    WHEN 1 
    THEN MDCTable.MDLast 
    ELSE    
        CASE MDFormat1Count
        WHEN 1 
        THEN MDFormat1.MDFormat1Name
        ELSE MDCTable.MDLast + ' ' + upper(left(MDCTable.MDFirst, 1)) + 
                  MDCTable.MDInit
        END
    END AS MDFormattedName
FROM MDCTable 
INNER JOIN 
(
    SELECT COUNT(MDLast) as NameCount, MDLast
    FROM MDCTable
    GROUP BY MDLast
) MDCCount ON MDCCount.MDLast = MDCTable.MDLast
INNER JOIN (
    SELECT COUNT(MDLast + left(MDFirst, 1)) as MDFormat1Count, MDLast + ' ' + 
         left(MDFirst, 1) AS MDFormat1Name
    FROM MDCTable
    GROUP BY MDLast + ' ' + left(MDFirst, 1) 
) MDFormat1 ON MDCTable.MDLast + ' ' + left(MDCTable.MDFirst, 1) = 
     MDFormat1.MDFormat1Name
ORDER BY MDCTable.MDC