Duplicate results - cannot condense

Question

The results from this report list the same client 7-9 times. I'm not entirely certain why. Have tried GROUPING and DISTINCT. DISTINCT refines the results to only 3 duplicates per Client.

Perhaps I am overlooking something obvious.

In short, I am getting

ClientID | First_Name | Last_Name

123.................John........................John

123.................John........................John

123.................John........................John

456.................Bob..........................Bob

456.................Bob..........................Bob

456.................Bob..........................Bob

What I need:

ClientID | First_Name | Last_Name

123.................John........................John

456.................Bob..........................Bob

But GROUP BY or DISTINCT are not resolving the issue.

SELECT  
     c.clientID
    ,c.firstName AS 'First_Name'
    ,c.lastName  AS 'Last_Name'
    ,c.homePhone AS 'Home'
    ,c.cellPhone AS 'Cell'
    ,NULL        AS '------'
    ,CASE
        WHEN ccf.name LIKE 'First Contact Date' THEN ccv.TextVal
     END         AS 'First_Contact_Date'
    ,CASE
        WHEN ccf.name LIKE 'Referral Date' THEN ccv.DateVal
     END         AS 'Referral_Date'
    ,CASE
        WHEN ccf.name LIKE 'Referred By' THEN ccv.TextVal
     END         AS 'Referred_By'
    ,CASE
        WHEN ccf.name LIKE 'Intake Date' THEN ccv.DateVal
     END         AS 'Intake_Date'
    ,CASE
        WHEN ccf.name LIKE 'Intake By' THEN ccv.TextVal
     END         AS 'Intake_By'
    ,CASE
        WHEN ccf.name LIKE 'Orientation Date' THEN ccv.TextVal
     END         AS 'Orientation_Date'
    ,CASE
        WHEN civ.clientIndexID = 5 THEN civ.clientIndexValueName
     END         AS 'Primary_Language'
    ,NULL        AS '------'
    ,CASE
        WHEN civ.clientIndexID = 18 THEN civ.clientIndexValueName
     END         AS 'Type'
    ,CASE
        WHEN civ.clientIndexID = 20 THEN civ.clientIndexValueName
     END         AS 'Stage_at_intro'
    ,CASE
        WHEN civ.clientIndexID = 21 THEN civ.clientIndexValueName
     END         AS 'Current_stage'
    ,NULL        AS '------'
    ,CASE
        WHEN civ.clientIndexID = 19 THEN civ.clientIndexValueName
     END         AS 'Percentage'
    ,CASE
        WHEN civ.clientIndexID = 26 THEN civ.clientIndexValueName
     END         AS 'Location'
    ,CASE
        WHEN civ.clientIndexID = 27 THEN civ.clientIndexValueName
     END         AS 'Eligible?'

FROM clients c
    INNER JOIN tblClientCustomValues ccv
        ON c.clientID = ccv.clientID
    INNER JOIN tblClientCustomFields ccf
        ON ccv.ID = ccf.ID
    INNER JOIN tblClientIndexData cid
        ON c.clientID = cid.clientID
    INNER JOIN tblClientIndexValue civ
        ON cid.clientIndexValueID = civ.clientIndexValueID
GROUP BY c.clientID
        ,c.firstName
        ,c.lastName
        ,c.homePhone
        ,c.cellPhone
        ,ccf.name
        ,ccv.TextVal
        ,ccv.DateVal
        ,civ.clientIndexValueName
        ,civ.clientIndexID

More Information about tables:

tblClientCustomFields ccf:

ID | Name | TypeID

tblClientIndexData cid:

ClientIndexValueID | ClientID

tblClientIndexValue civ:

ClientIndexValueID | ClientIndexID | ClientIndexValueName

tblClientCustomValues ccv:

ID | ClientID | TextVal | DateVal | NumVal

Answer 1

You may remove info-columns from group-by list and add max() to your code or put all aggregation into subquery:

SELECT  
     c.clientID
    ,c.firstName AS 'First_Name'
    ,c.lastName  AS 'Last_Name'
    ,c.homePhone AS 'Home'
    ,c.cellPhone AS 'Cell'
    ,NULL        AS '------'
    ,info.*
FROM clients c
OUTER APPLY
(
  SELECT
    max(CASE
        WHEN ccf.name LIKE 'First Contact Date' THEN ccv.TextVal
     END)         AS 'First_Contact_Date'
    ,max(CASE
        WHEN ccf.name LIKE 'Referral Date' THEN ccv.DateVal
     END)         AS 'Referral_Date'
    ,max(CASE
        WHEN ccf.name LIKE 'Referred By' THEN ccv.TextVal
     END)         AS 'Referred_By'
    ,max(CASE
        WHEN ccf.name LIKE 'Intake Date' THEN ccv.DateVal
     END)         AS 'Intake_Date'
    ,max(CASE
        WHEN ccf.name LIKE 'Intake By' THEN ccv.TextVal
     END)         AS 'Intake_By'
    ,max(CASE
        WHEN ccf.name LIKE 'Orientation Date' THEN ccv.TextVal
     END)         AS 'Orientation_Date'
    ,max(CASE
        WHEN civ.clientIndexID = 5 THEN civ.clientIndexValueName
     END)         AS 'Primary_Language'
    ,NULL        AS '------'
    ,max(CASE
        WHEN civ.clientIndexID = 18 THEN civ.clientIndexValueName
     END)         AS 'Type'
    ,max(CASE
        WHEN civ.clientIndexID = 20 THEN civ.clientIndexValueName
     END)         AS 'Stage_at_intro'
    ,max(CASE
        WHEN civ.clientIndexID = 21 THEN civ.clientIndexValueName
     END)         AS 'Current_stage'
    ,max(CASE
        WHEN civ.clientIndexID = 19 THEN civ.clientIndexValueName
     END)         AS 'Percentage'
    ,max(CASE
        WHEN civ.clientIndexID = 26 THEN civ.clientIndexValueName
     END)         AS 'Location'
    ,max(CASE
        WHEN civ.clientIndexID = 27 THEN civ.clientIndexValueName
     END)         AS 'Eligible?'
    FROM tblClientCustomValues ccv
    INNER JOIN tblClientCustomFields ccf
        ON ccv.ID = ccf.ID
    INNER JOIN tblClientIndexData cid
        ON ccv.clientID = cid.clientID
    INNER JOIN tblClientIndexValue civ
        ON cid.clientIndexValueID = civ.clientIndexValueID
    WHERE c.clientID = ccv.clientID
) info