Query??

  • How can i turn.

    Category          Diagnosis

    A                       apple

    A                      banana

    A                        peas

    B                         apple

    B                           banana

    B                          peas

    C                           apple

    into the following resultset:

    Category       Apple           banana           peas

    A                 yes             yes                 yes

    B                  yes              yes                yes

    C                 yes               no                  no

     

     

    or

     

    Category          Apple            banana           peas

    A                      YES               NULL             NULL

    A                    NULL                    YES          NULL 

    A                    NULL               NULL              YES

    B                     YES

    B                                             YES

    C                   

    A

  • Create a temp table with columns:

    Category , Apple, banana  , peas

    Insert distinct categories in to the temp table.

    then loop thru the original table to update temp table inside the loop.

    eg:

    If Diagnosis='Apple'

    update temptable set Apple='Y' where category = @categoty

    If Diagnosis='banana'

    update temptable set banana='Y' where category = @categoty

    If Diagnosis='peas'

    update temptable set peas='Y' where category = @categoty

    Select * from temptable

  • don't understand........

     

    what is the @category for?

    any other suggestions guys?

  • Would this be what you mean?

    SELECTCASE WHEN(COUNT(CASE WHEN Diagnosis = 'Apple' THEN 1 END) = 1) THEN 'yes' ELSE 'no' END AS [Apple],

    CASE WHEN(COUNT(CASE WHEN Diagnosis = 'Banana' THEN 1 END) = 1) THEN 'yes' ELSE 'no' END AS [Banana],

    CASE WHEN(COUNT(CASE WHEN Diagnosis = 'Peas' THEN 1 END) = 1) THEN 'yes' ELSE 'no' END AS [peas]

    FROM test2

    GROUP by Category

    Jan

  • Thanks Jan ur a genious...... worked like a charm

Viewing 5 posts - 1 through 5 (of 5 total)

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