Post reply

How to merge when one field has two values

  • November 21, 2008 at 1:23 pm

    #124962

    I have a table Item

    Create table #Item ( Item varchar(40), IsNew varchar(1))

    Insert into #Item values ('ABC','N')

    Insert into #Item values ('ABC','Y')

    Insert into #Item values ('DEF','Y')

    Insert into #Item values ('GHI','N')

    Insert into #Item values ('JKL','N')

    Insert into #Item values ('JKL','Y')

    When I run for the table I have, (N and Y means No and Yes)

    ABCN

    ABCY

    DEFY

    GHIN

    JKLN

    JKLY

    We can see ABC and JKL items have ‘Yes’ and ‘No’. So when one Item has both ‘Yes’ and ‘No’ then I need to change it to ‘No’. So I should get result like

    ABCN

    DEFY

    GHIN

    JKLN

    Can you please help me?

  • November 25, 2008 at 9:28 am

    #903242

    Will this do?

    select ITEM, CASE WHEN COUNT(ITEM) > 1 THEN 'N' ELSE 'Y' END

    from #item

    GROUP BY ITEM

  • November 25, 2008 at 2:45 pm

    #903445

    thank you, that worked.

  • November 25, 2008 at 2:50 pm

    #903449

    No problem!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply