I am trying to use case and like statement. When I run this below query it is duplicating results on some rows where it is y on particular columns. I am using SQL Server 2000.

```
SELECT DISTINCT [rjvn_pound_reference]
    ,t_reference
    ,t_street_name
    ,t_zone_name
    ,(
        CASE 
            WHEN rjvn_note LIKE '%CORRESPONDENCE%'
                THEN 'Y'
            ELSE 'N'
            END
        ) AS CorrespondenceReceived
    ,(
        CASE 
            WHEN rjvn_note LIKE '%review form complete%'
                THEN 'Y'
            ELSE 'N'
            END
        ) AS REVIEWFORMCOMPLETE
    ,(
        CASE 
            WHEN rjvn_note LIKE '%Manually issued NTO - Drive off%'
                THEN 'Y'
            ELSE 'N'
            END
        ) AS ManuallyissuedNTO
    ,(
        CASE 
            WHEN rjvn_note LIKE '%Manually issued NTK - Drive Off%'
                THEN 'Y'
            ELSE 'N'
            END
        ) AS ManuallyissuedNTK
    ,(
        CASE 
            WHEN rjvn_note LIKE '%Drive off- Final reminder sent%'
                THEN 'Y'
            ELSE 'N'
            END
        ) AS finalReminderSent
FROM [ICPS].[dbo].[removal_job_vehicle_notes] AS a
INNER JOIN tickets t ON (a.rjvn_pound_reference = t.t_number)
INNER JOIN dbo.ticket_hold_record thr ON (t.t_number = thr.thr_system_ref)
WHERE thr_hold_type = '2207555'
    AND t.t_contract IN ('18','1')

```

Results:

```
805464  XL05512211  Selby (60-100)  Abbey Walk  N   N   Y   N   N
805464  XL05512211  Selby (60-100)  Abbey Walk  N   Y   N   N   N
805464  XL05512211  Selby (60-100)  Abbey Walk  Y   N   N   N   N

```

But I want this to displayed on single row instead like:

```
805464  XL05512211  Selby (60-100)  Abbey Walk  Y   Y   Y   N   N.

UPDATED :
944786  XL07075730  Selby (60-100)  Abbey Walk  N   N   N   N   Y   
but I should get
944786  XL07075730  Selby (60-100)  Abbey Walk  N   N   Y   N   Y   

```

MY ACTUAL TABLE DATA for that record :

```
rjvn_pound_reference    rjvn_time                 rjvn_userid      rjvn_note
944786                  2014-01-08 10:38:17.000   JAMIED           Vehicle Drove Away During PCN Issue
944786                  2014-01-08 14:22:21.000   ADRIANJ          Correspondent Scanned
944786                  2014-02-05 15:44:53.000   ELLIED           Manually issued NTO drive off
944786                  2015-03-12 10:16:53.000   KATIEB           Drive off- Final reminder sent

--manually issued NTO drive off is displayed as 'N' instead of 'y'

```